Mysql必练50题第五天

第二十一题
查询不同老师所教不同课程平均分从高到低显示

select t_name,c_name,round(avg(sc.s_score),2) as avg_score from score sc left join 
course c on sc.c_id = c.c_id left join teacher t 
on c.t_id = t.t_id group by t.t_id order by avg_score desc;

第二十二题
查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select s_id,rank_num,s_score,c_id from
(select s_id,(row_number() over(partition by c_id order by s_score desc)) as rank_num,
s_score,c_id from score) as t where rank_num in (2,3);

第二十三题
统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select sc.c_id,c.c_name,
sum(case when s_score between 85 and 100 then 1 else 0 end) as "[100-85]",
concat(round(sum(case when s_score between 85 and 100 then 1 else 0 end)/count(*)*100,2),"%") as "百分比",
sum(case when s_score between 70 and 85 then 1 else 0 end) as "[85-70]",
concat(round(sum(case when s_score between 70 and 85 then 1 else 0 end)/count(*)*100,2),"%") as "百分比",
sum(case when s_score between 60 and 70 then 1 else 0 end) as "[70-60]",
concat(round(sum(case when s_score between 60 and 70 then 1 else 0 end)/count(*)*100,2),"%") as "百分比",
sum(case when s_score between 0 and 60 then 1 else 0 end) as "[0-60]",
concat(round(sum(case when s_score between 0 and 60 then 1 else 0 end)/count(*),2),"%") as "百分比"
from score sc left join course c on sc.c_id = c.c_id group by c_id;

第二十四题
查询学生平均成绩及其名次

select s.s_name,s.s_id,round(avg(sc.s_score),2) as avg_score,
(rank() over(order by avg(sc.s_score) desc)) as score_order
from student s inner join score sc on s.s_id = sc.s_id group by sc.s_id;

第二十五题
查询各科成绩前三名的记录

select s.s_id,sc.c_id,s.s_name,c.c_name,sc.t1
from  student s inner join 
(select s_id,c_id,(row_number() over(partition by c_id order by s_score desc)) as t1 from score) sc
on s.s_id = sc.s_id
inner join course c on sc.c_id = c.c_id
where sc.t1 in (1,2,3) order by sc.c_id;
``

![在这里插入图片描述](https://img-blog.csdnimg.cn/f3c124768adb4fe2b902669d58e950fc.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAWllYR1c=,size_15,color_FFFFFF,t_70,g_se,x_16)