select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from
score;t1
2、根据是否大于平均成绩记录flag,大于则记为0否则记为1
select
uid,
if(score>avg_score,0,1) flag
from
t1;t2
3、根据学生id进行分组统计flag的和,和为0则是所有学科都大于平均成绩
select
uid
from
t2
group by
uid
having
sum(flag)=0;
4、最终sql
select
uid
from
(
select
uid,
if(score > avg_score, 0, 1) flag
from
(
select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from
score
) t1
) t2
group by
uid
having
sum(flag)= 0;