所有考试科目的成绩都大于对应学科平均成绩的学生

有学生每科科目成绩，找出所有科目成绩都大于对应学科的平均成绩的学生

CREATE TABLE t9_scores (
sid bigint COMMENT '学生ID',
cid bigint COMMENT '课程ID',
score bigint COMMENT '得分'
) COMMENT '用户课程分数';

insert into t9_scores(sid,cid,score)
values
(1,1,90),
(1,2,50),
(1,3,72),
(2,1,40),
(2,2,50),
(2,3,22),
(3,1,30),
(3,2,50),
(3,3,52),
(4,1,90),
(4,2,90),
(4,3,72)

1.使用窗口函数给每行记录添加对应科目的平均成绩 

select sid,cid,score,avg(score) over(partition by cid) avg_score
from t9_scores

2.给每个学生每个科目进行打标，科目成绩>科目平均成绩的标记为0，反之为1

select sid,cid, if(score>avg_score,0,1) flag
from(
select sid,cid,score,avg(score) over(partition by cid) avg_score
from t9_scores) temp

3.使用sum统计flag和为0的学生即满足条件的学生

select sid from(
select sid,cid, if(score>avg_score,0,1) flag
from(
select sid,cid,score,avg(score) over(partition by cid) avg_score
from t9_scores) temp) temp1
group by sid
having sum(flag)=0