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

Естественно, нумероваться строки должны в соответствии с некоторым порядком. Пусть этот порядок задается столбцом первичного ключа, то есть в порядке возрастания (или убывания) значений в этом единственном столбце сортировки. Для определенности предположим, что нам нужно перенумеровать модели в таблице 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:

Решение 8.2.1

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

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

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

Если ваша СУБД поддерживает ранжирующие функции, то пронумеровать строки можно совсем просто:

SELECT ROW_NUMBER() OVER(ORDER BY model) no, model
FROM Product;

Решить задачу на SQL-EX.RU

Несколько усложним задачу, и попытаемся пронумеровать модели каждого производителя отдельно. Воспользуемся предыдущим решением, и внесем в него следующие изменения:

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

SELECT COUNT(*) no, P2.model
FROM Product P1 
    JOIN Product P2 ON P1.maker =P2.maker 
         AND P1.model <= P2.model
GROUP BY P2.model
ORDER BY P2.model;

В принципе, это и все. Правда, результат не отличается наглядностью.

2. Добавим в вывод производителя, при этом все равно из какой таблицы мы его возьмем в силу равенства. Однако тогда необходимо добавить производителя в столбцы группировки (MySQL не в счет):

SELECT COUNT(*) no, P1.maker, P2.model
FROM Product P1 
    JOIN Product P2 ON P1.maker =P2.maker 
         AND P1.model <= P2.model
GROUP BY P1.maker, P2.model
ORDER BY P2.model;

3. Ну и, наконец, добавим сортировку для наглядности результата. Столбец maker должен быть первым столбцом сортировки, чтобы каждая группа выводилась отдельно.

SELECT COUNT(*) no, P1.maker, P2.model
FROM Product P1 
    JOIN Product P2 ON P1.maker =P2.maker 
         AND P1.model <= P2.model
GROUP BY P1.maker, P2.model
ORDER BY P1.maker, P2.model;

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

Ранжирующие функции, естественно, упрощают запрос:

SELECT ROW_NUMBER() OVER(PARTITION BY maker ORDER BY model) no, 
       maker, model
FROM Product
ORDER BY maker, model;