Эмуляция функции 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, 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;

+------+------+-------+-----------+-----------+---------------+--------+
| num  | aid  | uid   | lname     | fname     | sname         | status |
+------+------+-------+-----------+-----------+---------------+--------+
|    1 | 1004 | 40251 | Жиг.....  | Світлана  | Леонідівна    |      9 |
|    1 | 1005 | 40047 | Бур...    | Ольга     | Володимирівна |      9 |
|    1 | 1014 | 40919 | Ман...    | Андрій    | Миколайович   |      9 |
|    1 | 1018 | 40444 | Син....   | Марія     | Олександрівна |      9 |
|    2 | 1018 | 40400 | Лом....   | Маргарита |               |      9 |
|    3 | 1018 | 40001 | Син....   | Олександр | Миколайович   |      9 |
|    4 | 1018 | 40800 | Син....   | Микола    | Андрійович    |      6 |
|    1 | 1019 | 40119 | Люб...... | Катерина  | Миколаївна    |      9 |
|    1 | 1020 | 40946 | Гур.....  | Станислав | Вячеславович  |      0 |
|    1 | 1022 | 40407 | Гор..     | Леонід    | Григорович    |      9 |
+------+------+-------+-----------+-----------+---------------+--------+
10 rows in set (0,001 sec)

І варіант, коли треба зупиняти лічильник якщо значення повторюються:

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;

+------+------+-------+-----------+-----------+---------------+--------+
| num  | aid  | uid   | lname     | fname     | sname         | status |
+------+------+-------+-----------+-----------+---------------+--------+
|    1 | 1004 | 40251 | Жиг.....  | Світлана  | Леонідівна    |      9 |
|    2 | 1005 | 40047 | Бур...    | Ольга     | Володимирівна |      9 |
|    3 | 1014 | 40919 | Ман...    | Андрій    | Миколайович   |      9 |
|    4 | 1018 | 40444 | Син....   | Марія     | Олександрівна |      9 |
|    4 | 1018 | 40400 | Лом....   | Маргарита |               |      9 |
|    4 | 1018 | 40001 | Син....   | Олександр | Миколайович   |      9 |
|    4 | 1018 | 40800 | Син....   | Микола    | Андрійович    |      6 |
|    5 | 1019 | 40119 | Люб...... | Катерина  | Миколаївна    |      9 |
|    6 | 1020 | 40946 | Гур.....  | Станислав | Вячеславович  |      0 |
|    7 | 1022 | 40407 | Гор..     | Леонід    | Григорович    |      9 |
+------+------+-------+-----------+-----------+---------------+--------+
10 rows in set (0,001 sec)

Мы использовали оператор 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;