MySQL номер строки

В SQL есть специальная функция вывода номеров строк в запросе ROW_NUMBER(). Но в его младшем брате MySQL нет такой функции. Единственный способ вывести номер строки в MySQL - использование переменной.

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

Получать данные будем из таблицы истории получения очков, которая зовётся "history". Выглядит она так:

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

SELECT SUM(POINTS) as 'POINTS', NAME FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC;

База вернёт следующую таблицу:

Отлично! Теперь надо пронумеровать строки, чтобы получить место пользователя в рейтинге. Алексей будет первым, Сергей - вторым, Виталий - третьим.

Нумеровка делается через переменную, добавляемую к выводу. Казалось бы, можно сделать всё просто:

SET @rank=0;

SELECT @rank:=@rank+1 AS 'RANK', SELECT SUM(POINTS) as 'POINTS', NAME FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC;

Но если сделать такой запрос, то результат будет плачевным. Назначение рейтинга произойдёт после группировки, но до сортировки ORDER BY SUM(POINTS). Выглядеть результат будет так:

Цифры ранга идут не по порядку: 1, 3, 2. Чтобы исправить положение необходимо добавлять столбец ранга после группировки и сортировки, в отдельном запросе. По логике, получается SELECT в SELECT. Синтаксис будет такой:

SET @rank=0;
SELECT @rank:=@rank+1 AS 'RANK', POINTS, NAME FROM
(

SELECT SUM(POINTS) as 'POINTS', NAME
FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC

) as t ;

Обратите внимание на окончание запроса. Без "as t;" написать запрос нельзя, иначе будет ошибка "Every derived table must have its own alias!".

Результат такого запроса будет выглядеть как нормальная ранговая таблица. С правильным расположением цифр ранга:

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

Представим, что нам нужно вывести номер ранга в личный кабинет пользователя. Чтобы Алексей видел в своём личном кабинете цифру "1", Сергей - "2", а Виталий - "3". Тогда запрос будет содержать SELECT внутри SELECT внутри SELECT. Вот так:

SET @rank=0;
SELECT RANK FROM
(

SELECT @rank:=@rank+1 AS 'RANK', NAME FROM
(

SELECT SUM(POINTS), NAME
FROM `history` GROUP BY NAME ORDER BY SUM(POINTS) DESC

) as t1

) as t2
WHERE NAME = 'Виталий';

Обратите внимание на хвосты: "as t1", "as t2". Нужны разные псевдонимы, иначе будет ошибка "Every derived table must have its own alias!".

P.S.

Конечно же, на реальном проекте такая таблица будет содержать id пользователя, а не его имя. Потому что в строке правды нет, а в числе есть 🙂