MySQL查询选择具有自动增量的结果作为结果中添加的新列 [英] MySQL query to select results with auto increment as a new column added in the result

问题描述

我有一个带有以下字段的学生桌:

I have a student's table with the following fields:

student(student_id, student_name, student_avg)

我需要在MySQL中编写一个查询，将查询结果显示为:

I need to write a query in MySQL which will display the result as :

序列号=>结果中还应该有一个新列，其序列号为1,2,3,...,n，就像结果中每一行的自动递增一样.

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.

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.

示例数据:

student_id         student_name      student_avg 
 1                    abc               2.5
 2                    xyz               4.1
 3                    def               4.2     

查询后的示例输出:

serial_no    student_id    student_name     student_avg
  1             2            xyz               4.1
  2             3            def               4.2

推荐答案

尝试一下

SELECT  @s:=@s+1 serial_number,student_id,student_name,student_avg
FROM    students,
        (SELECT @s:= 0) AS s
WHERE
student_avg > 4;

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

这篇关于MySQL查询选择具有自动增量的结果作为结果中添加的新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！