使用MySQL，如何生成包含表中记录索引的列？
有什么办法可以从查询中获取实际的行号？
我希望能够通过一个名为score的字段来订购一个名为League_girl的表；并返回用户名和该用户名的实际行位置。
我想对用户进行排名，以便我可以告诉特定用户在哪里。乔在200中排名100，即
User Score Row Joe 100 1 Bob 50 2 Bill 10 3 我在这里看到了一些解决方案，但是我已经尝试了大多数解决方案，但实际上没有一个返回行号。
我已经试过了：
SELECT position, username, score FROM (SELECT @row := @row + 1 AS position, username, score FROM league_girl GROUP BY username ORDER BY score DESC) 作为派生
...但是它似乎没有返回行位置。
有任何想法吗？
写回答
-
您可能需要尝试以下方法：
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命令。
测试用例：
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)您可能需要尝试以下方法：
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命令。
测试用例：
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)您可能需要尝试以下方法：
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命令。
测试用例：
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)来源：stack overflow
2020-05-10 17:14:42赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关文章
相关电子书
更多
