問題描述
有什么辦法可以從查詢中獲取實際的行號?
Is there any way I can get the actual row number from a query?
我希望能夠通過名為 score 的字段訂購名為 League_girl 的表；并返回用戶名和該用戶名的實際行位置.
I want to be able to order a table called league_girl by a field called score; and return the username and the actual row position of that username.
我想對用戶進行排名，這樣我就可以知道特定用戶在哪里，即.Joe 在 200 個中排名第 100，即
I'm wanting to rank the users so i can tell where a particular user is, ie. Joe is position 100 out of 200, i.e.
User Score Row
Joe 100 1
Bob 50 2
Bill 10 3
我在這里看到了一些解決方案，但我已經嘗試了其中的大部分，但沒有一個真正返回行號.
I've seen a few solutions on here but I've tried most of them and none of them actually return the row number.
我已經試過了:
SELECT position, username, score
FROM (SELECT @row := @row + 1 AS position, username, score
FROM league_girl GROUP BY username ORDER BY score DESC)
作為衍生
...但它似乎沒有返回行位置.
...but it doesn't seem to return the row position.
有什么想法嗎?
推薦答案
您可能想嘗試以下操作:
You may want to try the following:
SELECT l.position,
l.username,
l.score,
@curRow := @curRow + 1 AS row_number
FROM league_girl l
JOIN (SELECT @curRow := 0) r;
JOIN (SELECT @curRow := 0) 部分允許變量初始化，而無需單獨的 SET 命令.
The JOIN (SELECT @curRow := 0) part allows the variable initialization without requiring a separate SET command.
測試用例:
CREATE TABLE league_girl (position int, username varchar(10), score int);
INSERT INTO league_girl VALUES (1, 'a', 10);
INSERT INTO league_girl VALUES (2, 'b', 25);
INSERT INTO league_girl VALUES (3, 'c', 75);
INSERT INTO league_girl VALUES (4, 'd', 25);
INSERT INTO league_girl VALUES (5, 'e', 55);
INSERT INTO league_girl VALUES (6, 'f', 80);
INSERT INTO league_girl VALUES (7, 'g', 15);
測試查詢:
SELECT l.position,
l.username,
l.score,
@curRow := @curRow + 1 AS row_number
FROM league_girl l
JOIN (SELECT @curRow := 0) r
WHERE l.score > 50;
結果:
+----------+----------+-------+------------+
| position | username | score | row_number |
+----------+----------+-------+------------+
| 3 | c | 75 | 1 |
| 5 | e | 55 | 2 |
| 6 | f | 80 | 3 |
+----------+----------+-------+------------+
3 rows in set (0.00 sec)
這篇關于使用 MySQL，如何生成包含表中記錄索引的列?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！