В этой статье я расскажу вам, как пронумеровать строки результата запроса, возвращаемого 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 employeesLIMIT5;
В выше приведённом запросе мы:
Определили переменную row_number и инициализировали её нулевым значением;
Увеличивали её значение на 1 при каждой итерации запроса.
Другая техника, позволяющая достичь того же результата, заключается в создании вместо глобальной переменной производной таблицы и перекрёстном объединении этих двух таблиц. Пример такого запроса:
SELECT (@row_number:=@row_number +1) AS num, firstName, lastNameFROM employees,(SELECT @row_number:=0) AS tLIMIT5;
Обратите внимание на то, что для соблюдения правил синтаксиса у производной таблицы должен быть псевдоним.
Возобновление нумерации в группах
Как нам задать отдельную нумерацию для каждой группы строк, объединённых выражением ORDER BY или GROUP BY? Например, как имитировать следующий запрос:
SELECT customerNumber, paymentDate, amountFROM paymentsORDER BY customerNumber;
Нам нужно сформировать список платежей, в котором каждому платежу будет соответствовать определённый порядковый номер. Для того чтобы получить требуемый результат, нам понадобятся две переменные: одна – с порядковым номером строки, другая – для хранения идентификатора клиента из предыдущей строки, чтобы сравнить его с текущим. Наш запрос будет выглядеть так:
SELECT @row_number:=CASE WHEN @customer_no = customerNumber THEN @row_number + 1 # ! здається помилка: "..._no = CustomerNumber THEN..."
ELSE1ENDAS num, @customer_no:=customerNumber as CustomerNumber, paymentDate, amountFROM paymentsORDER BY customerNumber;
Мій приклад:
SELECT @row_number:=CASEWHEN @aid_no = aid THEN @row_number +1ELSE1ENDAS num, @aid_no:=au.aid as aid, au.uid, u.lname, u.fname, u.sname, au.statusFROM au, uWHERE u.uid=au.uidORDER BY au.aid, statusDESCLIMIT10;
Мы использовали оператор CASE для вычисления условия: если номер клиента остаётся прежним, мы увеличиваем номер строки на 1, в противном случае мы устанавливаем номер строки равным 1. Результат будет тем же, что и на выше приведённом скриншоте.
Теперь добьёмся того же результата, используя технику производной таблицы и перекрёстного запроса:
SELECT @row_number:=CASEWHEN @customer_no = customerNumber THEN @row_number +1ELSE1ENDAS num, @customer_no:=customerNumber as CustomerNumber, paymentDate, amountFROM payments,(SELECT @customer_no:=0,@row_number:=0) as tORDER BY customerNumber;