如何编写SQL查询找出平均成绩最高学生的rollno？

现有数据表 #

student表 #

marks表 #

平均分查询及结果 #

执行以下SQL可计算每个学生的平均分：

select rollno,(mar+eng+maths+phy+chem)/5 as average from marks;

得到结果：

错误语句分析 #

你尝试的语句存在语法错误，因为MAX()聚合函数不能直接嵌套返回多行结果的子查询：

select rollno,max(
                    select (mar+eng+maths+phy+chem)/5 from marks
                    ) 
from marks;

正确SQL语句 #

方法一：子查询匹配最高平均分 #

这种写法兼容所有主流数据库，能找出所有平均分等于最高分的学生rollno：

SELECT rollno
FROM marks
WHERE (mar + eng + maths + phy + chem)/5 = (
    SELECT MAX((mar + eng + maths + phy + chem)/5)
    FROM marks
);

方法二：使用窗口函数（适用于MySQL 8+、PostgreSQL等） #

通过窗口函数给平均分排名，筛选排名第一的学生，同样支持并列最高分的场景：

SELECT rollno
FROM (
    SELECT rollno,
           (mar + eng + maths + phy + chem)/5 AS average,
           RANK() OVER(ORDER BY (mar + eng + maths + phy + chem)/5 DESC) AS rnk
    FROM marks
) t
WHERE rnk = 1;

内容的提问来源于stack exchange，提问作者samarjeet desai