MySQL - 在 Select 语句中获取对应的行号

我可以运行一个 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中处理它。

更新

设置rank将它添加到结果集中，但没有正确排序:

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)

420649

小开

最佳答案

看看这。

更改您的查询为:

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

最后一个选择是计数。

小开

你可以使用MySQL变量来实现。类似这样的方法应该可以工作(不过，它由两个查询组成)。

SELECT 0 INTO @x;


SELECT itemID,
COUNT(*) AS ordercount,
(@x:=@x+1) AS rownumber
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;

小开

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

小开

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;