需求:找出每门课程都高于班级课程平均分的学生
1.查出每门课程的平均分
2.计算每个学生每门课程与平均分的相减结果
3.查出相减结果最小值大于0
select 班级id,学生id
from (
select t1.班级id,t1.学生id,t1.课程id,t1.成绩,
t1.成绩 - t2.课程平均分 as 相减结果
from 学生成绩表 as t1
left join (
select 班级id,课程id,avg(成绩) as 课程平均分
from 学生成绩表
group by 班级id,课程id
) as t2 on t1.班级id = t2.班级id and t1.课程id = t2.课程id
) as tmp
group by 班级id,学生id
having min(相减结果) > 0;