如何在 MySQL 查询结果中显示序列号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6055791/
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
How to show sequential number in MySQL query result
提问by nunu
I have some simple query:
我有一些简单的查询：
SELECT foo, bar FROM table
i think you now whats the result looks like.
我想你现在的结果是什么样的。
What I want to do is to show some sequential number based on how many data appear from query result. its just like AUTO_INCREMENT(its not mean i want to show ID).
The result what I want is like:
我想要做的是根据查询结果中出现的数据数量显示一些序列号。它就像AUTO_INCREMENT（这并不意味着我想显示 ID）。我想要的结果是这样的：
|No| foo | bar |
-------------------------
|1 | bla | 123 |
|2 | boo | abc |
|3 | wow | xxx |
How should I do to make it?
我该怎么做才能做到？
thanks in advance
提前致谢
回答by bungdito
select @rownum:=@rownum+1 No, foo, bar from table, (SELECT @rownum:=0) r;
回答by divinedragon
The order gets scrambled if you are using GROUP BYclause in your query. The work around is putting your query inside the FROMclause like this.
如果您GROUP BY在查询中使用子句，则订单会被打乱。解决方法是将您的查询放在这样的FROM子句中。
SET @a:=0;
SELECT @a:=@a+1 No, output.*
FROM (
SELECT foo, bar
FROM table GROUP BY foo, bar
) output;
回答by Travis Heeter
Neither of the answers worked for me, but based on bungdito's source, I realized you can do something easier:
这两个答案都不适合我，但根据bungdito的来源，我意识到你可以做一些更简单的事情：
SET @a:=0;
SELECT @a:=@a+1 No, foo, bar
FROM table;
So, first make sure SET @a:=0;runs. That will set up your variable, a. Then you can increment it in the results with @a:=@a+1. I tested this with GROUP BY, ORDER BY, even JOINS and it worked as expected.
所以，首先确保SET @a:=0;运行。这将设置您的变量 a。然后你可以在结果中增加它@a:=@a+1。我用 GROUP BY、ORDER BY 甚至 JOINS 测试了这个，它按预期工作。
回答by Arvind Krmar
If somebody wants to display the row number after ordering records, something like this may work
如果有人想在订购记录后显示行号，这样的事情可能会奏效
set @a=0;
select @a:=@a+1 serial_number,t.*
from ( SELECT foo, bar FROM tableORDER BY bar ) t
