問題描述

如果項目已排序，我可以運行 select 語句并獲取行號嗎?

Can I run a select statement and get the row number if the items are sorted?

我有一張這樣的桌子:

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 獲取訂單數:

I can then run this query to get the number of orders by ID:

SELECT itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID ORDER BY ordercount DESC;

這給了我表格中每個 itemID 的計數，如下所示:

This gives me a count of each itemID in the table like this:

+--------+------------+
| itemID | ordercount |
+--------+------------+
|    388 |          3 |
|    234 |          2 |
|   3432 |          1 |
|    693 |          1 |
|   3459 |          1 |
+--------+------------+

我也想得到行號，所以我可以知道 itemID=388 是第一行，234 是第二行，等等(本質上是訂單，而不僅僅是原始計數).我知道當我得到結果集時我可以在 Java 中執行此操作，但我想知道是否有一種方法可以完全在 SQL 中處理它.

I want to get the row number as well, so I could tell that itemID=388 is the first row, 234 is 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.

更新

設置等級會將其添加到結果集中，但未正確排序:

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)

推薦答案

看看這個.

將您的查詢更改為:

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

最后一個選擇是你的計數.

The last select is your count.

這篇關于MySQL - 在選擇時獲取行號的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！