MySQL 在mysql中选择增量计数器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13566695/
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
select increment counter in mysql
提问by iJade
Here is my mysql query
这是我的 mysql 查询
select name from table;
I want to select an increment counter along side name.How to do this. so the expected output will be
我想沿着名称选择一个增量计数器。如何做到这一点。所以预期的输出将是
Jay 1
roy 2
ravi 3
ram 4
回答by juergen d
select name,
@rownum := @rownum + 1 as row_number
from your_table
cross join (select @rownum := 0) r
order by name
This part:
这部分：
cross join (select @rownum := 0) r
makes it possible to introduce a variable without the need of a seperate query. So the first query could also be broken down into two queries like this:
可以在不需要单独查询的情况下引入变量。所以第一个查询也可以分解成两个这样的查询：
set @rownum := 0;
select name,
@rownum := @rownum + 1 as row_number
from your_table
order by name;
for instance when used in a stored procedure.
例如在存储过程中使用时。
回答by user11415449
In MySQL 8 and above you can also use the ROW_NUMBER()Window function.
在 MySQL 8 及更高版本中，您还可以使用 ROW_NUMBER()Window 函数。
SELECT
name,
ROW_NUMBER() OVER ()
FROM table
Result:
结果：
Jay 1
roy 2
ravi 3
ram 4
As shown by juergen d, it would be a good idea to put an ORDER BYto have a deterministic query.
如 juergen d 所示，将 an 放入ORDER BY确定性查询是个好主意。
The ORDER BYcan apply to the query and the counter independently. So:
该ORDER BY可申请查询和独立的计数器。所以：
SELECT
name,
ROW_NUMBER() OVER (ORDER BY name DESC)
FROM table
ORDER BY name
would give you a counter in decreasing order.
会给你一个降序的计数器。
Result:
结果：
Jay 4
ram 3
ravi 2
roy 1
回答by Nili Waypa
SELECT name,
@rownum := @rownum + 1 as row_number
FROM your_table
,
(select @rownum := 0) r
I prefer using a comma instead of CROSS JOINas it performs faster. Using CROSS JOINwill add one extra step of adding a column to your table.
我更喜欢使用逗号而不是CROSS JOIN因为它执行得更快。使用CROSS JOIN将添加一个额外的步骤，即向表中添加一列。
