在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:=@rank+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)
看看这个。
将您的查询改为：
SET @rank=0;
SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC;
SELECT @rn:=@rn+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;
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;