ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
(4 оценок, среднее: 5,00 из 5)
Загрузка...
Описание : в этой статье вы узнаете о функции ROW_NUMBER() в MySQL и о том, как использовать ее для генерации последовательного числа для каждой строки в наборе результатов.
MySQL представил эту функцию ROW_NUMBER() с версии 8.0. ROW_NUMBER() является оконной функцией или аналитическая функция, которая присваивает порядковый номер каждой строке, к которому она применяется, начиная с одной.
Ниже показан синтаксис функции ROW_NUMBER():
ROW_NUMBER() OVER (<partition_definition> <order_definition>)
partition_definition имеет следующий синтаксис:
PARTITION BY <expression>,[{,<expression>}...]
Предложение PARTITION BY разбивает строки на меньшие множества. Выражение может быть любым допустимым выражением, которое будет использоваться в предложении GROUP BY. Вы можете использовать несколько выражений, разделенных запятыми.
PARTITION BY не является обязательным. Если вы его опустите, весь набор результатов будет считаться разделом. Однако, когда вы используете предложение PARTITION BY, каждый раздел также может рассматриваться как окно.
order_definition синтаксически выглядит следующим образом :
ORDER BY <expression> [ASC|DESC],[{,<expression>}...]
Цель ORDER BY – установить порядок строк. Предложение ORDER BY не зависит от запроса ORDER BY.
Давайте использовать таблицу products из примера базы данных для демонстрации:
Следующий оператор использует функцию ROW_NUMBER() для назначения последовательного номера каждой строке таблицы products:
SELECT ROW_NUMBER() OVER ( ORDER BY productName ) row_num, productName, msrp FROM products ORDER BY productName;
Вы можете использовать функцию ROW_NUMBER() для запросов, которые находят первые N строк для каждой группы, например, три лучших сотрудника по продажам каждого канала продаж, пять лучших высокопроизводительных продуктов каждой категории.
Следующее утверждение находит три лучших продукта, которые имеют самый высокий запас в каждой товарной линейке:
WITH inventory AS (SELECT productLine, productName, quantityInStock, ROW_NUMBER() OVER ( PARTITION BYproductLine ORDER BY quantityInStock DESC) row_num FROM products ) SELECT productLine, productName, quantityInStock FROM inventory WHERE row_num <= 3;
В этом примере
Вы можете использовать, ROW_NUMBER() чтобы превратить неуникальные строки в уникальные строки, а затем удалить дублирующиеся строки . Рассмотрим следующий пример.
Сначала создайте таблицу с некоторыми повторяющимися значениями:
CREATE TABLE t ( id INT, name VARCHAR(10) NOT NULL ); INSERT INTOt(id,name) VALUES(1,'A'), (2,'B'), (2,'B'), (3,'C'), (3,'C'), (3,'C'), (4,'D');
Во-вторых, используйте функцию ROW_NUMBER() для разделения строк на разделы по всем столбцам. Номер строки будет перезапущен для каждого уникального набора строк.
SELECT id, name, ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id) AS row_num FROM t;
Как видно из вывода, уникальные строки – это те, чей номер строки равен единице.
В-третьих, вы можете использовать общее табличное выражение (CTE) для возврата дублирующихся строк и удалить оператор для удаления:
WITH dups AS (SELECT id, name, ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id) AS row_num FROM t) DELETE FROM t USING t JOIN dups ON t.id = dups.id WHERE dups.row_num <> 1;
Обратите внимание, что MySQL не поддерживает удаление на основе CTE, поэтому нам пришлось объединить исходную таблицу с CTE в качестве обходного пути.
Поскольку ROW_NUMBER() присваивает каждой строке в наборе результатов уникальный номер, вы можете использовать его для нумерации страниц.
Предположим, вам нужно отобразить список продуктов с 10 продуктами на странице. Чтобы получить товары для второй страницы, вы используете следующий запрос:
SELECT * FROM (SELECT productName, msrp, row_number() OVER (order by msrp) AS row_num FROM products) t WHERE row_num BETWEEN 11 AND 20;
Из этой статьи вы узнали, как использовать функцию ROW_NUMBER() в MySQL для генерации последовательного номера для каждой строки в наборе результатов.
