使用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） part允许变量初始化而无需单独的 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屋！