使用 MySQL，如何生成包含表中记录索引的列? [英] With MySQL, how can I generate a column containing the record index in a table?

问题描述

有什么办法可以从查询中获取实际的行号?

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，如何生成包含表中记录索引的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！