Функция SQL Server ROW_NUMBER

В этом руководстве вы узнаете, как использовать функцию SQL Server ROW_NUMBER() для назначения последовательного целого числа каждой строке результирующего набора.

Введение в функцию SQL Server ROW_NUMBER()

ROW_NUMBER() — это оконная функция, которая присваивает последовательное целое число каждой строке в разделе результирующего набора. Номер строки начинается с 1 для первой строки в каждом разделе.

Ниже показан синтаксис функции ROW_NUMBER():

ROW_NUMBER() OVER(
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

Давайте подробно рассмотрим синтаксис функции ROW_NUMBER().

РАЗДЕЛЕНИЕ ПО

Предложение PARTITION BY делит результирующий набор на разделы(другой термин для групп строк). Функция ROW_NUMBER() применяется к каждому разделу отдельно и повторно инициализирует номер строки для каждого раздела.

Предложение PARTITION BY необязательно. Если вы его пропустите, функция ROW_NUMBER() будет обрабатывать весь результирующий набор как один раздел.

ЗАКАЗАТЬ ПО

Предложение ORDER BY определяет логический порядок строк в каждом разделе набора результатов. Предложение ORDER BY является обязательным, поскольку функция ROW_NUMBER() чувствительна к порядку.

Примеры функции SQL Server ROW_NUMBER()

Мы будем использовать таблицу sales.customers из примера базы данных для демонстрации функции ROW_NUMBER().

Пример использования функции SQL Server ROW_NUMBER() для результирующего набора

Следующий оператор использует функцию ROW_NUMBER() для присвоения каждой строке клиента порядкового номера:

SELECT 
   ROW_NUMBER() OVER(
 ORDER BY first_name
   ) row_num,
   first_name, 
   last_name, 
   city
FROM 
   sales.customers;

Вот частичный вывод:

В этом примере мы пропустили предложение PARTITION BY, поэтому функция ROW_NUMBER() обработала весь набор результатов как один раздел.

Пример использования SQL Server ROW_NUMBER() для разделов

В следующем примере функция ROW_NUMBER() используется для назначения последовательного целого числа каждому клиенту. Она сбрасывает номер при изменении города:

SELECT 
   first_name, 
   last_name, 
   city,
   ROW_NUMBER() OVER(
      PARTITION BY city
      ORDER BY first_name
   ) row_num
FROM 
   sales.customers
ORDER BY 
   city;

На следующем рисунке показан частичный вывод:

В этом примере мы использовали предложение PARTITION BY для разделения клиентов на разделы по городу. Номер строки был повторно инициализирован при изменении города.

Использование SQL Server ROW_NUMBER() для пагинации

Функция ROW_NUMBER() полезна для разбиения на страницы в приложениях. Например, вы можете отобразить список клиентов постранично, где каждая страница имеет 10 строк.

В следующем примере функция ROW_NUMBER() используется для возврата клиентов из строк 11–20, что соответствует второй странице:

WITH cte_customers AS(
    SELECT 
        ROW_NUMBER() OVER(
             ORDER BY 
                first_name, 
                last_name
        ) row_num, 
        customer_id, 
        first_name, 
        last_name
     FROM 
        sales.customers
) SELECT 
    customer_id, 
    first_name, 
    last_name
FROM 
    cte_customers
WHERE 
    row_num > 10 AND 
    row_num <= 20;

Вывод следующий:

В этом примере:

• Во-первых, CTE использовала функцию ROW_NUMBER() для назначения каждой строке в наборе результатов последовательного целого числа.
• Во-вторых, внешний запрос вернул строки второй страницы, имеющие номера от 11 до 20.

Краткое содержание

• Используйте функцию ROW_NUMBER(), чтобы назначить последовательное целое число каждой строке в разделе запроса.