表结构：uid,subject_id,score
求：找出所有科目成绩都大于某一学科平均成绩的学生
数据集如下

1）建表语句
create table score(
uid string,
subject_id string,
score int)
row format delimited fields terminated by '\t';
2）求出每个学科平均成绩
select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from
score;t1
3）根据是否大于平均成绩记录flag，大于则记为0否则记为1
select
uid,
if(score>avg_score,0,1) flag
from
t1;t2
4）根据学生id进行分组统计flag的和，和为0则是所有学科都大于平均成绩
select
uid
from
t2
group by
uid
having
sum(flag)=0;
5）最终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;