Новое в стандарте и реализациях языка SQL

Функции ранжирования

В MySQL ранжирующих/оконных функций не было до версии 8.0, однако была возможность использовать переменные непосредственно в запросе SQL. В частности, с помощью переменных можно решить задачу нумерации строк запроса. Продемонстрируем это на примере, который рассматривался на предыдущей странице.

SELECT @i:=@i+1 num, 
     trip_no, id_comp
    FROM Trip, (SELECT @i:=0) X
    WHERE ID_comp < 3
    ORDER BY id_comp, trip_no;

В третьей строке запроса выполняется инициализация переменной и присваивается ей начальное значение. В итоге каждая строка таблицы Trip будет соединяться со строкой из одного столбца, содержащего 0 (просто декартово произведение).

В первой строке запроса значение переменной инкрементируется на 1, что происходит при вычислении каждой следующей строки в порядке, заданном предложением ORDER BY. В итоге мы получаем нумерацию строк в порядке сортировки.

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

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

Консоль

Выполнить

    SELECT row_number() over(PARTITION BY id_comp ORDER BY id_comp,trip_no) num,
     trip_no, id_comp 
    FROM Trip
    WHERE ID_comp < 3
    ORDER BY ID_comp, trip_no;

    SELECT row_number() over(PARTITION BY id_comp ORDER BY id_comp,trip_no) num,      trip_no, id_comp     FROM Trip     WHERE ID_comp < 3     ORDER BY ID_comp, trip_no;

    Схема

Нет схемы

 Идея решения состоит в следующем. Введем еще одну переменную для хранения номера компании. При инициализации присвоим ей несуществующий номер (например, 0). Затем для каждой строки будем проверять, совпадает ли номер с номером компании текущей строки. Если значения совпадают, будем инкрементировать, если нет, сбрасывать в 1. Наконец, будем присваивать переменной номер компании из текущей строки. Дело в том, что проверка выполняется до присвоения, тем самым мы сравниваем текущее значение номера компании с номером компании из предыдущей строки (в заданном порядке сортировки). Теперь сам запрос.

SELECT 
CASE WHEN @comp=id_comp THEN @i:=@i+1 ELSE @i:=1 END num,
@comp:=id_comp id_comp, trip_no
    FROM Trip, (SELECT @i:=0, @comp:=0) X
    WHERE ID_comp < 3
    ORDER BY ID_comp, trip_no;

Или, коль скоро вы отошли от стандарта, можно использовать функцию IF, чтобы сократить запись:

SELECT 
IF(@comp=id_comp, @i:=@i+1, @i:=1) num,
@comp:=id_comp id_comp, trip_no
    FROM Trip, (SELECT @i:=0, @comp:=0) X
    WHERE ID_comp < 3
    ORDER BY id_comp, trip_no;

Проверить эти запросы вы можете из консоли, выбрав из списка MySQL.