小编典典

MySQL - 在选择时获取行号

all

如果项目已排序,我可以运行 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)

阅读 74

收藏
2022-07-08

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

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

2022-07-08