Эмуляция функции row_number() в MySQL

В данной статье я расскажу вам, как пронумеровать строки результата запроса, возвращаемого MySQL.

Функцию row_number() – это функцию ранжирования, возвращающая порядковый номер строки, начиная с 1 для первой строки. Номер строки часто бывает нужен при генерации отчётов. Эта функцию реализована в MS SQL и в Oracle. В MySQL подобная функцию отсутствует, но её несложно реализовать за счёт глобальных переменных.

Нумерация строк

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

SET @row_number = 0;SELECT(@row_number:=@row_number + 1) AS num, firstName, lastNameFROM employeesLIMIT 5;

Эмуляция возможности row_number() в MySQL В выше приведённом запросе мы:

  • Определили переменную row_number и инициализировали её нулевым значением;
  • Увеличивали её значение на 1 при каждой итерации запроса.

Другая техника, позволяющая достичь того же результата, заключается в создании вместо глобальной переменной производной таблицы и перекрёстном объединении данных двух таблиц. Пример такого запроса:

SELECT(@row_number:=@row_number + 1) AS num, firstName, lastNameFROM employees,(SELECT @row_number:=0) AS tLIMIT 5;

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

Возобновление нумерации в группах

Как нам задать отдельную нумерацию для каждой группы строк, объединённых выражением ORDER BY или GROUP BY? К примеру, как имитировать следующий запрос:

SELECT customerNumber, paymentDate, amountFROM paymentsORDER BY customerNumber;

Эмуляция возможности row_number() в MySQL Нам необходимо сформировать список платежей, в котором каждому платежу будет соответствовать определённый порядковый номер. Для того чтобы приобрести требуемый результат, нам понадобятся две переменные: одна – с порядковым номером строки, другая – для хранения идентификатора покупателя из предыдущей строки, чтобы сравнить его с текущим. Наш запрос будет выглядеть так:

SELECT @row_number:=CASE WHEN @customer_no = customerNumber THEN @row_number + 1 ELSE 1 END AS num, @customer_no:=customerNumber as CustomerNumber, paymentDate, amountFROM paymentsORDER BY customerNumber;

Мы использовали оператор CASE для вычисления условия: если номер покупателя остаётся прежним, мы увеличиваем номер строки на 1, в противном случае мы устанавливаем номер строки равным 1. Результат будет тем же, что и на выше приведённом скриншоте.

Сейчас добьёмся того же результата, используя технику производной таблицы и перекрёстного запроса:

SELECT @row_number:=CASE WHEN @customer_no = customerNumber THEN @row_number + 1 ELSE 1 END AS num, @customer_no:=customerNumber as CustomerNumber,  paymentDate,    amountFROM payments,(SELECT @customer_no:=0,@row_number:=0) as tORDER BY customerNumber;

Итак, мы научились эмулировать нумерацию строк запроса в MySQL.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *