MySQL MySQL查询选择自动增量的结果作为结果中添加的新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11861799/
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
MySQL query to select results with auto increment as a new column added in the result
提问by Aayush
I have a student's table with the following fields:
我有一个包含以下字段的学生表：
student(student_id, student_name, student_avg)
I need to write a query in MySQL which will display the result as :
我需要在 MySQL 中编写一个查询，它将结果显示为：
Serial no. => the result should also have a new column with serial number as 1,2,3,...,nlike an auto increment for each row in the result.
序列号。=> 结果还应该有一个带有序列号的新列，1,2,3,...,n就像结果中每一行的自动增量一样。
student_id
student_name
student_avg > 4
I don't want to alter my table in any way. All I have to do is write a query which will give me the above result. I hope I am clear.
我不想以任何方式改变我的桌子。我所要做的就是写一个查询，它会给我上面的结果。我希望我很清楚。
Example data:
示例数据：
student_id student_name student_avg
1 abc 2.5
2 xyz 4.1
3 def 4.2
Sample output after querying:
查询后的示例输出：
serial_no student_id student_name student_avg
1 2 xyz 4.1
2 3 def 4.2
回答by
Try this on
试试这个
SELECT @s:=@s+1 serial_number,student_id,student_name,student_avg
FROM students,
(SELECT @s:= 0) AS s
WHERE
student_avg > 4;
回答by Edward Ruchevits
SET @serial=0;
SELECT @serial := @serial+1 AS `serial_number`, `column_name` FROM `table_name`;
In your particular case:
在您的特定情况下：
SET @serial=0;
SELECT
@serial := @serial+1 AS `serial_number`,
`student_id`,
`student_name`,
`student_avg`
FROM
`students`
WHERE
`student_avg` > 4;
