头歌实验MySQL数据库 - 复杂查询（二）

第1关：查询学生平均分

#请在此添加实现代码
########## Begin ##########
select student.s_id, student.s_name,round(avg(score.s_score),2) as avg_score 
from student inner join score on student.s_id=score.s_id
group by s_id having avg_score<60

union
select student.s_id,student.s_name,0 as avg_score 
from student 
where student.s_id not in(select distinct s_id from score);


########## End ##########

第2关：查询修课相同学生信息

#请在此添加实现代码
########## Begin ##########
create view temp as(select s_id,group_concat(c_id)as c from score group by s_id);
select * from student where s_id in(select s_id from temp where c=(select c from temp where s_id="01")and s_id<>"01");
########## End ##########

第3关：查询各科成绩并排序

#请在此添加实现代码
########## Begin ##########
/*select s_id,c_id,s_score,
rank() over (partition by c_id order by s_score desc) as rank
from score;
order by s_id,c_id;*/
SELECT 
    s.s_id,
    s.c_id,
    s.s_score,
    (SELECT COUNT(*) + 1 
     FROM score s2 
     WHERE s2.c_id = s.c_id AND s2.s_score > s.s_score) AS rank
FROM score s
ORDER BY s.s_id,s.c_id;
########## End ##########

第4关：查询张老师课程成绩最高的学生

#请在此添加实现代码
########## Begin ##########
select a.*,b.s_score,b.c_id,c.c_name from student a 
INNER JOIN score b ON a.s_id = b.s_id
INNER JOIN course c ON b.c_id = c.c_id
where b.c_id = (select c_id from course c,teacher d where c.t_id=d.t_id and d.t_name="张三")
and b.s_score in (select MAX(s_score)from score where c_id="02");

########## End ##########

第5关：查询两门课程不及格同学信息

#请在此添加实现代码
########## Begin ##########
select a.s_id,a.s_name,ROUND(AVG(b.s_score))
avg_score from student a 
inner join score b on a.s_id = b.s_id
where a.s_id in(
    select s_id from score where s_score<60 GROUP BY s_id having count(*)>=2
)
GROUP BY a.s_id,a.s_name;

########## End ##########