sql题-所有课程大于60分

题目

有三张表：

student学生表中属性有 id(int), student_name(varchar)

class课程表中属性有 id(int), class_name(varchar)

sc选课表中属性有 sid(int), cid(int), score(int)

一个学生可以选多门课，一门课可以有多个学生，每个课程分数都大于60分意味着这个学生得分最低的课程大于60分。

with t0 as (
select 1 as id,'yuxing01' as student_name
union all 
select 2 as id,'yuxing02' as student_name
union all 
select 3 as id,'yuxing03' as student_name
)
,t1 as (
select 1001 as id,'语文' as class_name 
union all 
select 1002 as id,'数学' as student_name
union all 
select 1003 as id,'英语' as student_name    
)
,t2 as (
select 1 as sid,1001 as cid,61 as score
union all 
select 1 as sid,1002 as cid,62 as score
union all 
select 1 as sid,1002 as cid,63 as score
union all 
select 2 as sid,1001 as cid,59 as score
union all 
select 2 as sid,1002 as cid,71 as score
union all 
select 3 as sid,1002 as cid,71 as score
union all 
select 3 as sid,1003 as cid,72 as score
)

这道题具有迷惑性，其实重点关注这个sc表就行了，其他属性后面再关联也行，同时要注意这个关键条件，是所有课程都要及格，只及格一个还是不够的，语兴这里列举4种不同办法（其他的懒得写了）

方法1关联

既然你要全部大于60分的人，那我们直接自关联筛选min大于60分的人就行了，因此直接join，就可以取出

select t0.student_name 
      ,t1.class_name 
from  t0  
join t2 
on t0.id = t2.sid 
join  (
    select sid
          ,min(score) as min_score 
    from t2 group by sid having min(score)>60 
    ) t3 
on t0.id = t3.sid
join t1
on t2.cid = t1.id 

方法2数组排序

collect_list+sort_array 排序取第一位就行了，如果第一位都大于60后面的也不用考虑

select * from (
select sort_array(COLLECT_LIST(score))[0] as min_score
      ,sid
from t2
GROUP BY sid
) where min_score>60

方法3开窗

同理取first_value排序取第一位就行了，如果第一位都大于60后面的也不用考虑

select distinct sid from (
select FIRST_VALUE(score) over(PARTITION BY sid ORDER BY score asc) as min_score
      ,sid
from t2 
)
where min_score>60

方法4打标

只要课程大于60 我们就给他打标，再sum /课程数，如果为1说明都大于60了

select sum(is_60)/count( cid)
      ,sid
from (
SELECT if(score>60,1,0) as is_60
      ,sid
      ,cid
from t2
)
GROUP BY sid
having sum(is_60)/count( cid)=1