使用MySQL查询，以选择自动递增的结果作为添加到结果中的新列

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,...,n like an auto increment for each row in the result.

序列号。=>的结果也应该有一个新的列，序列号为1、2、3……，表示结果中每一行的自动增量。

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

2 个解决方案

#1

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;

https://*.com/a/11096550/1423506

#2

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;

#1