loading..
Русский    English
01:15
листать

Функция 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 CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX Больше тэгов
Учебник обновлялся
вчера
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100