MySQL - 在选择时获取行号
如果项目已排序，我可以运行 select 语句并获取行号吗?
Can I run a select statement and get the row number if the items are sorted?
我有一张这样的桌子:
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 获取订单数:
I can then run this query to get the number of orders by ID:
SELECT itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID ORDER BY ordercount DESC;
这给了我表格中每个 itemID 的计数，如下所示:
This gives me a count of each itemID in the table like this:
+--------+------------+
| itemID | ordercount |
+--------+------------+
| 388 | 3 |
| 234 | 2 |
| 3432 | 1 |
| 693 | 1 |
| 3459 | 1 |
+--------+------------+
我也想得到行号，所以我可以知道 itemID=388 是第一行，234 是第二行，等等(本质上是订单，而不仅仅是原始计数).我知道当我得到结果集时我可以在 Java 中执行此操作，但我想知道是否有一种方法可以完全在 SQL 中处理它.
I want to get the row number as well, so I could tell that itemID=388 is the first row, 234 is second, etc (essentially the ranking of the orders, not just a raw count). I know I can do this in Java when I get the result set back, but I was wondering if there was a way to handle it purely in SQL.
更新
设置等级会将其添加到结果集中，但未正确排序:
Setting the rank adds it to the result set, but not properly ordered:
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;
最后一个选择是你的计数.
The last select is your count.
相关文章