MySQL - 在查询中获取行号

Question

如果数据已经排好序，我能运行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)

- George

---

未来参考：如果您想按照排名从1到5进行排序，请使用 ORDER BY rank ASC（按升序排列排名）。我猜这就是您所说的“但没有正确排序”的意思。 - BlueCacti

可能是MySQL中的ROW_NUMBER()重复问题。 - Ciro Santilli OurBigBook.com

Answer 1

看一下这个。

将你的查询更改为：

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

最后一个 select 是你的计数。

- Mike Cialowicz

---

这将排名添加到结果集中，但不会按正确顺序排序 - 已更新问题并附带结果。 - George

尝试保留 ORDER BY ordercount DESC，然后将整个查询语句包装在另一个 SELECT 中，该语句获取第一个查询中的所有内容，但按排名列（在此情况下为0）排序。 - Mike Cialowicz

你能展示一个例子吗？我该如何包装选择器？ - George

查看swamibebop的回答。 - thaddeusmt

@MikeCialowicz，这个不起作用。请参考我的解决方案或Swamibebop的解决方案获取正确答案。 - Pacerier

Answer 2

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

---

感谢您的澄清，这解决了我遇到的乱序问题。 - thaddeusmt

谢谢，这对我真的很有用 :) 我很惊讶居然没有更直接的方法从结果集中获取行“索引”...但无论如何感谢，这很方便。 - rat

你可以通过改变第一个SELECT语句中的查询来添加第四行，其中包含增量totalcount。查询语句为：SELECT @rn:=@rn+1 AS rank, itemID, ordercount, @tot:=@tot+ordercount as totalcount。要定义@tot的初始值，应在t2后添加此项：（SELECT @tot:=0）t3。删除每个\ @之前的\，这是我必须使用的以规避迷你Markdown格式。 - Jan Ehrhardt

有人能解释一下t1和t2的相关性吗？ - Jared

@Jared，MySQL语法只需要有一些东西在那里。它可以是任何东西，甚至是 x 和 y。 - Pacerier

Answer 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;

- Pacerier

---

你是否知道为什么在select语句中使用@r := @r + 1是有效的，但如果在存储过程中使用declare r int; set r = 0;，然后使用r := r +1会报错？ - Dan M.

@Pacerier，第二个select的行顺序在哪里得到保证？我知道没有order by子句的select返回的行顺序没有得到保证，而最外层的select恰好是从内部有序的select中选择的，因此它可能是一个例外。但如果不是这样，我就看不出这是一个正确的解决方案，因为它将与Chibu的Mike的解决方案存在相同的缺陷——不能保证select将按照哪个顺序遍历记录并标号。 - Dan M.

你有任何想法为什么ORDER BY在不在字段列表中时都无法工作吗？请看我的结果：https://hastebin.com/aluqefunoy.rb - Winter

Answer 4

您可以使用MySQL变量来实现。像这样的东西应该可以工作（尽管它由两个查询组成）。

SELECT 0 INTO @x;

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

- Chibu

---

小心，这个不会起作用，因为 order by 发生在变量 @x 被评估之后。尝试使用其他列进行排序。还要尝试使用 desc 和 asc。你会发现很多时候它们会失败，唯一能够工作的时候是由于你原始“select”的顺序与 order by 的顺序相同，这是纯粹的运气。请参考我的解决方案和/或 Swamibebop 的解决方案。 - Pacerier

@Pacerier 你确定吗？我在另一个例子中尝试了类似的查询（基本上是从数字列中选择，并根据它们的顺序编号），似乎如果按var/row num排序，当更改结果行的顺序时，但每个数字都有相同的行号。但如果按数字列排序，则 ASC / DESC 会更改这些数字编号的顺序（从最小到最大或反之亦然）。因此，在那种情况下，order by 首先被评估。 - Dan M.

Answer 5

现在已经内置在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

- caram

Answer 6

SELECT RANK() OVER(ORDER BY Employee.ID) rank, forename, surname, Department.Name, Occupation.Name  
FROM Employee  
JOIN Occupation ON Occupation.ID = Employee.OccupationID  
JOIN Department ON Department.ID = Employee.DepartmentID 
WHERE DepartmentID = 2;

- Clarius