MySQL - Получить номер строки на выбранном

Можно ли запустить оператор select и получить номер строки, если элементы отсортированы?

У меня есть таблица вроде этого:

mysql> describe orders;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| orderID     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| itemID      | bigint(20) unsigned | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

Затем я могу запустить этот запрос, чтобы получить количество заказов по ID:

SELECT itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID ORDER BY ordercount DESC;

Это дает мне счет каждого itemID в таблице следующим образом:

+--------+------------+
| itemID | ordercount |
+--------+------------+
|    388 |          3 |
|    234 |          2 |
|   3432 |          1 |
|    693 |          1 |
|   3459 |          1 |
+--------+------------+

Я хочу также получить номер строки, поэтому я мог сказать, что itemID=388 - это первая строка, 234 - вторая и т.д. (по существу, ранжирование заказов, а не только сырое количество). Я знаю, что могу сделать это на Java, когда получаю результат, но мне было интересно, есть ли способ обработать его только в SQL.

Обновление

Установка ранга добавляет его в результирующий набор, но не упорядочен правильно:

mysql> SET @rank=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @rank:[email protected]+1 AS rank, itemID, COUNT(*) as ordercount
    -> FROM orders
    -> GROUP BY itemID ORDER BY rank DESC;
+------+--------+------------+
| rank | itemID | ordercount |
+------+--------+------------+
|    5 |   3459 |          1 |
|    4 |    234 |          2 |
|    3 |    693 |          1 |
|    2 |   3432 |          1 |
|    1 |    388 |          3 |
+------+--------+------------+
5 rows in set (0.00 sec)

Ответы

Ответ 1

Посмотрите это.

Измените свой запрос на:

SET @rank=0;
SELECT @rank:[email protected]+1 AS rank, itemID, COUNT(*) as ordercount
  FROM orders
  GROUP BY itemID
  ORDER BY ordercount DESC;

Ответ 2

SELECT @rn:[email protected]+1 AS rank, itemID, ordercount
FROM (
  SELECT itemID, COUNT(*) AS ordercount
  FROM orders
  GROUP BY itemID
  ORDER BY ordercount DESC
) t1, (SELECT @rn:=0) t2;

Ответ 3

Решение Swamibebop работает, но, используя синтаксис table.*, Мы можем избежать повторения имен столбцов внутреннего select и получить более простой/короткий результат:

SELECT @r := @r+1 , 
       z.* 
FROM(/* your original select statement goes in here */)z, 
(SELECT @r:=0)y;

Так что это даст вам:

SELECT @r := @r+1 , 
       z.* 
FROM(
     SELECT itemID, 
     count(*) AS ordercount
     FROM orders
     GROUP BY itemID
     ORDER BY ordercount DESC
    )z,
    (SELECT @r:=0)y;

Ответ 4

Вы можете использовать переменные MySQL для этого. Что-то вроде этого должно работать (хотя оно состоит из двух запросов).

SELECT 0 INTO @x;

SELECT itemID, 
       COUNT(*) AS ordercount, 
       (@x:[email protected]+1) AS rownumber 
FROM orders 
GROUP BY itemID 
ORDER BY ordercount DESC;