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)

Answer 1

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

Answer 2

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;

Answer 3

如果有人想尝试使用单个查询的更简洁的方法，那么这可能是一个解决方案。

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

在这种情况下，您不需要指定默认为 null 的初始值，并且此 IFNULL 检查将为此查询分配 0 值。

Answer 4

看看这个。

将您的查询更改为：

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

最后一个选择是您的计数。

Answer 5

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;

Answer 6

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;

Answer 7

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

SELECT 0 INTO @x;

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