loading..
Русский    English
09:41
листать

Функция ROW_NUMBER стр. 1

Функция ROW_NUMBER, как следует из ее названия, нумерует строки, возвращаемые запросом. С ее помощью можно выполнить более сложное упорядочивание строк в отчете, чем то, которое дает предложение ORDER BY в рамках Стандарта SQL-92.

До появления этой функции для нумерации строк, возвращаемых запросом, приходилось использовать довольно сложный интуитивно непонятный алгоритм, изложенный в параграфе. Единственным достоинством данного алгоритма является то, что он будет работать практически на всех СУБД, поддерживающих стандарт SQL-92.

Примечание:

Естественно, можно выполнить нумерацию средствами процедурных языков, используя при этом курсоры и/или временные таблицы. Но мы здесь говорим о "чистом" SQL.

Используя функцию ROW_NUMBER можно:

  • задать нумерацию, которая будет отличаться от порядка сортировки строк результирующего набора;
  • создать "несквозную" нумерацию, т.е. выделить группы из общего множества строк и пронумеровать их отдельно для каждой группы;
  • использовать одновмеренно несколько способов нумерации, поскольку, фактически, нумерация не зависит от сортировки строк запроса.

Проще всего возможности функции ROW_NUMBER показать на простых примерах, к чему мы и переходим.

Пример 1.

Пронумеровать все рейсы из таблицы Trip в порядке возрастания их номеров. Выполнить сортировку по {id_comp, trip_no}.

Решение

Консоль
Выполнить
  1. SELECT row_number() over(ORDER BY trip_no) num,
  2. trip_no, id_comp
  3. FROM trip
  4. WHERE ID_comp < 3
  5. ORDER BY id_comp, trip_no

Предложение OVER, с которым используется функция ROW_NUMBER задает порядок нумерации строк. При этом используется дополнительное предложение ORDER BY, которое не имеет отношения к порядку вывода строк запроса. Если вы посмотрите на результат, то заметите, что порядок строк в результирующем наборе и порядок нумерации не совпадают:

num trip_no id_comp
3 1181 1
4 1182 1
5 1187 1
6 1188 1
7 1195 1
8 1196 1
1 1145 2
2 1146 2

Примечание:

Условие отбора id_comp<3 использовано лишь с целью уменьшения размера выборки.

Конечно, мы можем потребовать выдачу в порядке нумерации, переписав последнюю строку в виде

  1. ORDER BY trip_no
(или, что то же самое, order by num ).

Или, наоборот, пронумеровать строки в порядке заданной сортировки:

Консоль
Выполнить
  1. SELECT row_number() over(ORDER BY id_comp, trip_no) num,
  2. trip_no, id_comp
  3. FROM trip
  4. WHERE ID_comp<3
  5. ORDER BY id_comp, trip_no

num trip_no id_comp
1 1181 1
2 1182 1
3 1187 1
4 1188 1
5 1195 1
6 1196 1
7 1145 2
8 1146 2

А если требуется пронумеровать рейсы для каждой компании отдельно? Для этого нам потребуется еще одна конструкция в предложении OVER - PARTITION BY.

Конструкция PARTITION BY задает группы строк, для которых выполняется независимая нумерация. Группа определяется равенством значений в списке столбцов, перечисленных в этой конструкции, у строк, составляющих группу.

Пример 2.

Пронумеровать рейсы каждой компании отдельно в порядке возрастания номеров рейсов.


Консоль
Выполнить
  1. SELECT row_number() over(partition BY id_comp ORDER BY id_comp,trip_no) num,
  2. trip_no, id_comp
  3. FROM trip
  4. WHERE ID_comp < 3
  5. ORDER BY id_comp, trip_no

PARTITION BY id_comp означает, что рейсы каждой компании образуют группу, для которой и выполняется независимая нумерация. В результате получим:

num trip_no id_comp
1 1181 1
2 1182 1
3 1187 1
4 1188 1
5 1195 1
6 1196 1
1 1145 2
2 1146 2

Отсутствие конструкции PARTITION BY, как это было в первом примере, означает, что все строки результирующего набора образуют одну единственную группу.

Рекомендуемые упражнения: 65, 97, 116, 125, 130, 137


Bookmark and Share
Страницы: 1 2
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100