使用 MySQL，如何生成包含表中记录索引的列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3126972/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
With MySQL, how can I generate a column containing the record index in a table?
提问by TheBounder
Is there any way I can get the actual row number from a query?
有什么办法可以从查询中获取实际的行号？
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.
我希望能够通过名为 score 的字段订购名为 League_girl 的表；并返回用户名和该用户名的实际行位置。
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.
我想对用户进行排名，以便我可以知道特定用户在哪里，即。Joe 在 200 中的位置是 100，即
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.
我在这里看到了一些解决方案，但我已经尝试了其中的大部分，但实际上没有一个返回行号。
I have tried this:
我试过这个：
SELECT position, username, score
FROM (SELECT @row := @row + 1 AS position, username, score
FROM league_girl GROUP BY username ORDER BY score DESC)
As derived
作为派生
...but it doesn't seem to return the row position.
...但它似乎没有返回行位置。
Any ideas?
有任何想法吗？
回答by Daniel Vassallo
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;
The JOIN (SELECT @curRow := 0)part allows the variable initialization without requiring a separate SETcommand.
该JOIN (SELECT @curRow := 0)部件允许在不需要单独SET命令的情况下进行变量初始化。
Test case:
测试用例：
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);
Test query:
测试查询：
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;
Result:
结果：
+----------+----------+-------+------------+
| position | username | score | row_number |
+----------+----------+-------+------------+
| 3 | c | 75 | 1 |
| 5 | e | 55 | 2 |
| 6 | f | 80 | 3 |
+----------+----------+-------+------------+
3 rows in set (0.00 sec)
回答by Peter Johnson
SELECT @i:=@i+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo
回答by goadreamer
Here comes the structure of template I used:
这是我使用的模板结构：
select
/*this is a row number counter*/
( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 )
as rownumber,
d3.*
from
( select d1.* from table_name d1 ) d3
And here is my working code:
这是我的工作代码：
select
( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 )
as rownumber,
d3.*
from
( select year( d1.date ), month( d1.date ), count( d1.id )
from maindatabase d1
where ( ( d1.date >= '2013-01-01' ) and ( d1.date <= '2014-12-31' ) )
group by YEAR( d1.date ), MONTH( d1.date ) ) d3
回答by Hearth
You can also use
你也可以使用
SELECT @curRow := ifnull(@curRow,0) + 1 Row, ...
to initialise the counter variable.
初始化计数器变量。
回答by Heryno
If you just want to know the position of one specific user after order by field score, you can simply select all row from your table where field score is higher than the current user score. And use row number returned + 1 to know which position of this current user.
如果您只想在按字段分数排序后了解某个特定用户的位置，您可以简单地从表中选择字段分数高于当前用户分数的所有行。并使用返回的行号 + 1 来知道当前用户的位置。
Assuming that your table is league_girland your primary field is id, you can use this:
假设您的表是league_girl并且您的主要字段是id，您可以使用：
SELECT count(id) + 1 as rank from league_girl where score > <your_user_score>
回答by ftzdomino
Assuming MySQL supports it, you can easily do this with a standard SQL subquery:
假设 MySQL 支持它，您可以使用标准 SQL 子查询轻松完成此操作：
select
(count(*) from league_girl l1 where l2.score > l1.score and l1.id <> l2.id) as position,
username,
score
from league_girl l2
order by score;
For large amounts of displayed results, this will be a bit slow and you will want to switch to a self join instead.
对于大量显示的结果，这会有点慢，您需要切换到自联接。
回答by azerafati
I know the OP is asking for a mysqlanswer but since I found the other answers not working for me,
我知道 OP 正在寻求mysql答案，但由于我发现其他答案对我不起作用，
- Most of them fail with
order by - Or they are simply very inefficient and make your query very slow for a fat table
- 他们中的大多数都失败了
order by - 或者它们的效率非常低，并且使您的查询对于胖表非常缓慢
So to save time for others like me, just index the row after retrieving them from database
因此，为了节省像我这样的其他人的时间，只需在从数据库中检索它们后索引该行
example in PHP:
PHP 中的示例：
$users = UserRepository::loadAllUsersAndSortByScore();
foreach($users as $index=>&$user){
$user['rank'] = $index+1;
}
example in PHP using offset and limit for paging:
在 PHP 中使用偏移和限制进行分页的示例：
$limit = 20; //page size
$offset = 3; //page number
$users = UserRepository::loadAllUsersAndSortByScore();
foreach($users as $index=>&$user){
$user['rank'] = $index+1+($limit*($offset-1));
}
回答by BasicExp
I found the original answer incredibly helpful but I also wanted to grab a certain set of rows based on the row numbers I was inserting. As such, I wrapped the entire original answer in a subquery so that I could reference the row number I was inserting.
我发现原始答案非常有用，但我也想根据我插入的行号获取一组特定的行。因此，我将整个原始答案包装在一个子查询中，以便我可以引用我插入的行号。
SELECT * FROM
(
SELECT *, @curRow := @curRow + 1 AS "row_number"
FROM db.tableName, (SELECT @curRow := 0) r
) as temp
WHERE temp.row_number BETWEEN 1 and 10;
Having a subquery in a subquery is not very efficient, so it would be worth testing whether you get a better result by having your SQL server handle this query, or fetching the entire table and having the application/web server manipulate the rows after the fact.
在子查询中使用子查询不是很有效，因此值得测试是否通过让 SQL 服务器处理此查询获得更好的结果，或者获取整个表并让应用程序/Web 服务器在事后操作行.
Personally my SQL server isn't overly busy, so having it handle the nested subqueries was preferable.
就个人而言，我的 SQL 服务器并不太忙，因此最好让它处理嵌套的子查询。
