MySQL 在mysql查询中生成序列号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11094466/
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
Generate serial number in mysql query
提问by sushil
I have a table: student_marks
我有一张桌子：student_marks
marks
-----
44
55
64
98
76
Expected output:
预期输出：
serial_number|marks
--------------------
1 | 44
2 | 55
3 | 64
4 | 98
5 | 76
Using mysql user defined variables, it could be done using query:
使用 mysql 用户定义的变量，可以使用查询来完成：
set @a:=0;select @a:=@a+1 serial_number, marks from student_marks;
Is there any way to achieve this in msyql without using user defined variables?
有没有办法在不使用用户定义变量的情况下在 msyql 中实现这一点？
回答by GarethD
Based on your reasons for not wanting to use user defined variables as wanting to avoid having 2 queries, one for inializing and one to use it you could use the following:
根据您不想使用用户定义变量的原因，因为不想使用 2 个查询，一个用于初始化，另一个用于使用它，您可以使用以下内容：
SELECT @a:=@a+1 serial_number,
marks
FROM student_marks,
(SELECT @a:= 0) AS a;
回答by Kolajo
Best Answer to this question should be the following, the best practice
这个问题的最佳答案应该是以下，最佳实践
SET @count:=0;
SELECT (@count:=@count+1) AS serial_number,
marks
FROM student_marks
回答by Giovanny Canasto
I had a table with a column(c5) has contain a number x, I need a sql expression that repeated the same row x numbers of times:
我有一个带有列（c5）的表包含一个数字 x，我需要一个重复相同行 x 次的 sql 表达式：
my table A contains:
我的表 A 包含：
c1 c2 c3 c4 c5
16 1 2 16 3
16 1 2 17 2
16 1 2 18 1
and I need:
我需要：
c1 c2 c3 c4 c5 n
16 1 2 16 3 1
16 1 2 16 3 2
16 1 2 16 3 3
16 1 2 17 2 1
16 1 2 17 2 2
16 1 2 18 1 1
I solved That with the expression:
我用表达式解决了那个：
SELECT
c1, c2, c3, c4, c5, row_number AS n
FROM
(
SELECT
@curRow := @curRow + 1 AS row_number
FROM
tablea
JOIN (SELECT @curRow := 0) r
WHERE
@curRow < (
SELECT
max(field1)
FROM
tablea
)
) AS vwtable2
LEFT JOIN tablea d ON vwtable2.row_number <= tablea.field1;
回答by Krishna Kothoor
None of the above answers seemed satisfactory as you would need to initialize the variable as null in a separate query.
上述答案似乎都不令人满意，因为您需要在单独的查询中将变量初始化为 null。
So posting the below all encompassing solution:
因此，发布以下全方位解决方案：
SELECT ROUND(@a:=@a+1,0) sr_num, t.* from (select @a:=0) initvars, your_table t;
SELECT ROUND(@a:=@a+1,0) sr_num, t.* from (select @a:=0) initvars, your_table t;
回答by xdazz
No, there isn't. But you could produce the serial_number in program side, not the database side.
不，没有。但是您可以在程序端而不是数据库端生成serial_number。
回答by Mario The Spoon
Why don't you make it an auto-increment field?
你为什么不把它变成一个自动增量字段？
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
回答by CloudyMarble
No, unless you have a column which containes these numbers.
不，除非您有一列包含这些数字。
回答by Abhishek Madhani
Though very late for the answer, generating the sequential ids without having two queries and without using joins and without using sub query.
虽然答案很晚，但在没有两个查询、不使用连接和不使用子查询的情况下生成顺序 ID。
SELECT *, (@cnt := if(@cnt IS NULL, 0, @cnt) + 1) AS id FROM table_name;
Cheers
干杯
