MySQL - 在选择时获取行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2520357/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Get row number on select
提问by George
Can I run a select statement and get the row number if the items are sorted?
如果项目已排序，我可以运行 select 语句并获取行号吗？
I have a table like this:
我有一张这样的表：
mysql> describe orders;
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| orderID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| itemID | bigint(20) unsigned | NO | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
I can then run this query to get the number of orders by ID:
然后我可以运行此查询以按 ID 获取订单数：
SELECT itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID ORDER BY ordercount DESC;
This gives me a count of each itemIDin the table like this:
这给了我itemID表格中每个的计数，如下所示：
+--------+------------+
| itemID | ordercount |
+--------+------------+
| 388 | 3 |
| 234 | 2 |
| 3432 | 1 |
| 693 | 1 |
| 3459 | 1 |
+--------+------------+
I want to get the row number as well, so I could tell that itemID=388is the first row, 234is 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.
我也想获得行号，所以我可以知道这itemID=388是第一行，234是第二行，等等（基本上是订单的排名，而不仅仅是原始计数）。我知道当我得到结果集时我可以在 Java 中做到这一点，但我想知道是否有一种方法可以纯粹在 SQL 中处理它。
Update
更新
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)
采纳答案by Mike Cialowicz
回答by swamibebop
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;
回答by Pacerier
Swamibebop's solution works, but by taking advantage of table.*syntax, we can avoid repeatingthe column names of the inner selectand get a simpler/shorter result:
Swamibebop 的解决方案有效，但通过利用table.*语法，我们可以避免重复内部的列名select并获得更简单/更短的结果：
SELECT @r := @r+1 ,
z.*
FROM(/* your original select statement goes in here */)z,
(SELECT @r:=0)y;
So that will give you:
所以这会给你：
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;
回答by Chibu
You can use MySQL variables to do it. Something like this should work (though, it consists of two queries).
您可以使用 MySQL 变量来做到这一点。这样的事情应该可以工作（尽管它由两个查询组成）。
SELECT 0 INTO @x;
SELECT itemID,
COUNT(*) AS ordercount,
(@x:=@x+1) AS rownumber
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC;
回答by caram
It's now builtin in MySQL 8.0 and MariaDB 10.2:
它现在内置在 MySQL 8.0 和 MariaDB 10.2 中：
SELECT
itemID, COUNT(*) as ordercount,
ROW_NUMBER OVER (PARTITION BY itemID ORDER BY rank DESC) as rank
FROM orders
GROUP BY itemID ORDER BY rank DESC
