2021-04-29

手写HQL

表结构：uid,subject_id,score

求：找出所有科目成绩都大于某一学科平均成绩的学生

数据集如下

1001   01 90
1001   02 90
1001   03 90
1002   01 85
1002   02 85
1002   03 70
1003   01 70
1003   02 70
1003   03 85

1）建表语句

create table score(
    uid string,
    subject_id string,
    score int)
row format delimited fields terminated by '\t'; 

2）求出每个学科平均成绩

-- 以学科为分组，avg求平均数,保留两位小数
select
    subject_id,
    round(avg(score),2) avg
from
    score
group by
    subject_id;

3）根据是否大于平均成绩记录flag，大于则记为0否则记为1

-- 判断语句用刚学的if(boolean , 1, 0) 再用sum求和做出判断

4）根据学生id进行分组统计flag的和，和为0则是所有学科都大于平均成绩

-- sum求和
select
    uid,
    sum(if(score > t2.avg,1,0 )) flag
from score t1,
     (select
             subject_id,
     round(avg(score),2) avg
from
    score
group by
    subject_id) t2
where t1.subject_id = t2.subject_id
group by
    uid;

5）最终SQL

select *
from (select
          uid,
          sum(if(score > t2.avg,1,0 )) flag
      from score t1,
           (select
                subject_id,
                round(avg(score),2) avg
            from
                score
            group by
                subject_id) t2
where t1.subject_id = t2.subject_id
group by
          uid) w
where flag >=3;