使用 MySQL，如何生成包含表中记录索引的列?
有什么办法可以从查询中获取实际的行号?
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)
相关文章