Книги и статьи по SQL Rambler's Top100 Switch language to: English 14 июля 2020 г. 18:47:38


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Нумерация записей

Моисеенко С.И.

Обычно необходимость нумерации записей возникает при формировании отчетов. В этом случае нумерацию строк, возвращаемых запросом, обычно реализуют на клиенте. Например, не составляет особого труда перенумеровать строки отчета, подготовленного в MS Access. Однако иногда это необходимо сделать в самом запросе. Этот случай мы сейчас и рассмотрим.

Нумерация строк в соответствии с порядком значений первичного ключа

Естественно, нумероваться строки должны в соответствии с некоторым порядком. Пусть этот порядок задается полем первичного ключа, т.е. в порядке возрастания (или убывания) значений в этом поле. Для определенности предположим, что нам нужно перенумеровать модели в таблице Product, где номер модели является первичным ключом. Существенным здесь является то, значения первичного ключа не содержат дубликатов и NULL-значений, в результате чего имеется принципиальная возможность установить однозначное соответствие между номером модели и номером строки в порядке сортировки моделей.

Рассмотрим сначала следующий запрос:

SELECT P1.model, P2.model FROM Product P1 JOIN Product P2 ON P1.model <= P2.model

Здесь выполняется соединение двух одинаковых таблиц по неравенству P1.model <= P2.model, в результате чего каждая модель из второй таблицы (P2.model ) будет соединяться только с теми моделями из первой таблицы (P1.model), номера которых меньше или равны номеру этой модели. В результате получим, что модель с минимальным номером (1121) будет присутствовать во втором столбце результирующего набора только один раз, т.к. она меньше или равна только самой себе. На другом конце будет находиться модель с максимальным номером, т.к. любая модель будет меньше или равна ей. Следовательно, модель с максимальным номером будет сочетаться с каждой моделью, и число таких сочетаний будет равно общему числу моделей в таблице Product.

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

Таким образом, чтобы решить нашу задачу нумерации достаточно пересчитать модели в правом столбце, что нетрудно сделать при помощи группировки и использования агрегатной функции COUNT:

(1)   SELECT COUNT(*) no,P2.model FROM Product P1 JOIN Product P2
      ON P1.model <= P2.model GROUP BY P2.model

Не поленюсь и представлю результат выполнения этого запроса:

No model
1 1121
2 1232
3 1233
4 1260
5 1276
6 1288
7 1298
8 1321
9 1401
10 1408
11 1433
12 1434
13 1750
14 1752
15 2111
16 2112

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

Нумерация строк при наличии дубликатов в результирующем столбце

Согласно реляционной теории в таблице не может быть одинаковых строк. И хотя реализации допускают построение таблиц, не имеющих первичного ключа, и, как следствие, допускающих одинаковые строки, следует отнести эту ситуацию к ошибкам в проектировании. Кроме того, таблица, не имеющая первичного ключа или уникального индекса, не является обновляемой. Последнее заключение вполне естественно, т.к. система не имеет информации о том, какой из дубликатов предпочесть.

Поэтому, говоря о дубликатах, мы имеем в виду дубликаты в результирующем наборе, появление которых может быть обусловлено тем, что первичный ключ весь или частично (в случае составного ключа) отсутствует в результирующем наборе.

Чтобы пояснить сказанное, рассмотрим следующий запрос

SELECT id_psg FROM pass_in_trip

который вернет номера пассажиров, которые совершали полеты. Поскольку один и тот же пассажир может совершить несколько рейсов, мы получаем здесь дубликаты. Однако этот пассажир не может в один и тот же день более одного раза полететь одним и тем же рейсом, что регламентируется соответствующим первичным ключом - {trip_no, date, id_psg}.

Итак, нам нужно перенумеровать пассажиров, которые могут повторяться. Зададимся для начала порядком, в котором их нужно переименовать. Пусть этот порядок соответствует сортировке по трем полям - дата полета, идентификатор пассажира и номер рейса (по возрастанию).

Чтобы свести задачу к ранее рассмотренной (а это возможно, т.к. три перечисленных поля представляют собой первичный ключ), сконструируем столбец, который объединял бы информацию из перечисленных полей. Поскольку поля имеют разные типы данных, приведем их к символьному представлению и выполним конкатенацию.

При этом нам нужно определиться с количеством символов. Поскольку в представлении даты вылета отсутствует время, ограничимся 11 символами. Номер рейса везде представлен четырехсимвольным числом. Остается идентификатор пассажира. В соответствии с имеющейся базой данных ограничимся 2 символами, что не принижает общности подхода. Однако для правильности сортировки нужно "односимвольных" пассажиров записывать с лидирующим нулем - 01, 09 и т.д. Иначе пассажир с номером 10 будет предшествовать, скажем, 2-му. Выполним соответствующие преобразования:

(2)
   Дата - CAST(date AS CHAR(11))
   Номер рейса - CAST(trip_no AS CHAR(4))
   Идентификатор пассажира - RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2).

В последнем преобразовании (2) я использовал нестандартную функцию RIGHT (SQL Server), которая извлекает из строки указанное количество символов справа. Можно было бы использовать функцию SUBSTRING, однако так получается короче и, кроме того, наверняка в других коммерческих продуктах имеются аналогичные "улучшения" стандарта. Соединяя эти выражения в указанном порядке, мы получим уникальный столбец, который и будет использоваться для нумерации пассажиров в соответствии с возрастанием (убыванием) значений в этом столбце. Используя этот столбец, мы можем воспользоваться решением (1)(см. вып.5). Окончательно получим

SELECT COUNT(*) num, P2.id_psg FROM (
   SELECT *, CAST(date AS CHAR(11)) + RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+
      CAST(trip_no AS CHAR(4)) dit FROM pass_in_trip) P1 JOIN (
   SELECT *, CAST(date AS CHAR(11)) + RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+
      CAST(trip_no AS CHAR(4)) dit FROM pass_in_trip) P2
   ON P1.dit <= P2.dit
GROUP BY P2.dit, P2.id_psg
ORDER BY 1

Для нумерации в другом порядке достаточно конкатенировать преобразования (2) в другой последовательности. Например, чтобы пронумеровать пассажиров в порядке их идентификационных номеров, первым слагаемым должно быть RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2).

Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

На главную страницу

Print  Версия для печати


Использование любых материалов данного сайта возможно только
при условии обязательного размещения прямой ссылки на сайт
http://www.sqlbooks.ru
на каждой странице, где размещены используемые материалы.

 Начало   Статьи    Книги 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.