数据分析sql面试必会6题经典_经典SQL面试50题（二）

接上一次的更新：

21、查询不同老师所教不同课程平均分从高到低显示

#方法一：
#（没有生成新表，直接将score表和teacher表链接到course表中，然后按照
#a.c_id,a.t_id,c.t_name分组，再进行排序，最后查询）
select a.t_id,c.t_name,a.c_id,ROUND(avg(s_score),2) as avg_score 
from course a 
left join score b on a.c_id=b.c_id 
left join teacher c on a.t_id=c.t_id 
group by a.c_id,a.t_id,c.t_name 
order by avg_score desc;

#方法二：
#（思路：先从score表得到平均成绩并作为新表，然后将该表链接到course表并再次生成新表，
#然后再将新表链接到teacher表中，进行最后的查询）
select t.*,s.c_id,s.c_name,s.avg_score from teacher t 
join (
select c.*,s.avg_score from course c 
join (
select c_id,round(avg(s_score),2) as avg_score from score 
group by c_id 
order by avg_score desc) s 
on c.c_id=s.c_id) s 
on t.t_id=s.t_id;

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

select a.*,s.排名,s.s_score,s.c_id from(
select s.*,@i:=@i+1 as 排名 from score s,(select @i:=0) a 
where s.c_id='01' order by s.s_score desc) s 
left join student a on a.s_id=s.s_id 
where 排名 between 2 and 3 
union 
select a.*,s.排名,s.s_score,s.c_id from(
select s.*,@i:=@i+1 as 排名 from score s,(select @i:=0) a 
where s.c_id='02' order by s.s_score desc) s 
left join student a on a.s_id=s.s_id 
where 排名 between 2 and 3 
union 
select a.*,s.排名,s.s_score,s.c_id from(
select s.*,@i:=@i+1 as 排名 from score s,(select @i:=0) a 
where s.c_id='03' order by s.s_score desc) s 
left join student a on a.s_id=s.s_id 
where 排名 between 2 and 3 ;

分析：
先查询所有学生某一门课程的成绩，并进行排序，从而得到新表；
然后将该表和student表链接，并查询题目所需信息；
其次查询出排名第2名和第3名的学生，成为新表；
最后，进行前面三步，查询出所有课程的排名2、3名的学生，用union将所有表合并。

步骤：

答案：

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

select distinct f.c_name,a.c_id,
b.`85-100`,b.百分比,
c.`70-85`,c.百分比,
d.`60-70`,d.百分比,
e.`0-60`,e.百分比 
from score a 
left join 
(select c_id,sum(case when s_score >85 and s_score <=100 then 1 else 0 end) as `85-100`,
round(100*(sum(case when s_score >85 and s_score <=100 then 1 else 0 end)/count(*)),2) as 百分比 
from score group by c_id) b 
on a.c_id=b.c_id 
left join 
(select c_id,sum(case when s_score >70 and s_score <=85 then 1 else 0 end) as `70-85`,
round(100*(sum(case when s_score >70 and s_score <=85 then 1 else 0 end)/count(*)),2) as 百分比 
from score group by c_id) c 
on a.c_id=c.c_id 
le