如果项目已排序,我可以运行 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
+--------+------------+ | itemID | ordercount | +--------+------------+ | 388 | 3 | | 234 | 2 | | 3432 | 1 | | 693 | 1 | | 3459 | 1 | +--------+------------+
我也想得到行号,所以我可以说这itemID=388是第一行,234是第二行,等等(本质上是订单的排名,而不仅仅是原始计数)。我知道当我得到结果集时我可以在 Java 中执行此操作,但我想知道是否有一种方法可以纯粹在 SQL 中处理它。
itemID=388
234
更新
设置排名会将其添加到结果集中,但排序不正确:
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 @rank;
最后一个选择是你的计数。