В этой статье я расскажу вам, как пронумеровать строки результата запроса, возвращаемого MySQL.
Функция row_number() – это функция ранжирования, возвращающая порядковый номер строки, начиная с 1 для первой строки. Номер строки часто бывает нужен при генерации отчётов. Эта функция реализована в MS SQL и в Oracle. В MySQL подобная функция отсутствует, но её несложно реализовать за счёт глобальных переменных.
Нумерация строк
Чтобы пронумеровать строки, мы должны объявить переменную запроса. Продемонстрируем этот подход на примере простой таблицы, содержащей список работников предприятия (employees). Следующий запрос выбирает 5 работников из таблицы, присваивая им номера по порядку, начиная с 1:
SET @row_number = 0;
SELECT
(@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
employees
LIMIT 5;
В выше приведённом запросе мы:
Определили переменную row_number и инициализировали её нулевым значением;
Увеличивали её значение на 1 при каждой итерации запроса.
Другая техника, позволяющая достичь того же результата, заключается в создании вместо глобальной переменной производной таблицы и перекрёстном объединении этих двух таблиц. Пример такого запроса:
SELECT
(@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
employees,(SELECT @row_number:=0) AS t
LIMIT 5;
Возобновление нумерации в группах
Как нам задать отдельную нумерацию для каждой группы строк, объединённых выражением ORDER BY или GROUP BY? Например, как имитировать следующий запрос:
SELECT
customerNumber, paymentDate, amount
FROM
payments
ORDER BY customerNumber;
Нам нужно сформировать список платежей, в котором каждому платежу будет соответствовать определённый порядковый номер. Для того чтобы получить требуемый результат, нам понадобятся две переменные: одна – с порядковым номером строки, другая – для хранения идентификатора клиента из предыдущей строки, чтобы сравнить его с текущим. Наш запрос будет выглядеть так:
SELECT
@row_number:=CASE
WHEN @customer_no = customerNumber THEN @row_number + 1 # ! здається помилка: "..._no = CustomerNumber THEN..."
ELSE 1
END AS num,
@customer_no:=customerNumber as CustomerNumber,
paymentDate,
amount
FROM
payments
ORDER BY customerNumber;
Мій приклад:
SELECT
@row_number:=CASE
WHEN @aid_no = aid THEN @row_number + 1
ELSE 1
END AS num,
@aid_no:=au.aid as aid,
au.uid, u.lname, u.fname, u.sname, au.status
FROM
au, u
WHERE u.uid=au.uid
ORDER BY au.aid, status DESC
LIMIT 10;
І варіант, коли треба зупиняти лічильник якщо значення повторюються:
SET @row_number = 0;
SELECT
@row_number:=CASE
WHEN @aid_no = aid THEN @row_number
ELSE @row_number + 1
END AS num,
@aid_no:=au.aid as aid,
au.uid, u.lname, u.fname, u.sname, au.status
FROM
au, u
WHERE u.uid=au.uid
ORDER BY au.aid, status DESC
LIMIT 10;
Мы использовали оператор 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,
amount
FROM
payments,(SELECT @customer_no:=0,@row_number:=0) as t
ORDER BY customerNumber;
Обратите внимание на то, что для соблюдения у производной таблицы должен быть псевдоним.
