10-30 查询平均成绩最高的同学(MSSQL) (10 分)

Question

本题目要求编写SQL语句，查询平均成绩最高的同学的学号，姓名，性别，年龄及平均成绩。
注意：假设以当前系统时间为2020年来计算学生年龄。
提示：MSSQLServer 评测SQL语句
stu

sc表

输出
学号 姓名 性别 年龄 平均成绩
S005 张强 1 19 80.9

Answer 1

这个题里有个暗坑,sc表里存在有学生的部分科目分数为空,此时如果直接使用avg函数,会只对有分数的科目来进行平均计算,导致平均分偏高,因此需要把空的转化成0,或者使用sum(grade)/count(1)的方式。然后用top取排序后的第一行即可

select top 1 stu.sno,stu.sname,stu.sex,
datediff(year,stu.birdate,'2020-01-01') age, 
avg(isnull(grade,0)) avg_grade
from 
stu ,sc where stu.sno=sc.sno 
group by stu.sno,stu.sname,stu.sex,
datediff(year,stu.birdate,'2020-01-01')
order by 5 desc

或者使用开窗函数取排名

select sno,sname,sex,age,avg_grade
from (
select stu.sno,stu.sname,stu.sex,
datediff(year,stu.birdate,'2020-01-01') age, 
avg(isnull(grade,0)) avg_grade,
row_number() over(order by avg(isnull(grade,0)) desc) rn
from 
stu ,sc where stu.sno=sc.sno 
group by stu.sno,stu.sname,stu.sex,
datediff(year,stu.birdate,'2020-01-01')
) t where rn=1;