На главную страницу
Нумерация записей
Моисеенко С.И.
Обычно необходимость нумерации записей возникает при формировании отчетов. В
этом случае нумерацию строк, возвращаемых запросом, обычно реализуют на
клиенте. Например, не составляет особого труда перенумеровать строки отчета,
подготовленного в 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.
На главную страницу