图解面试题：经典50题
作者： 猴子数据分析
| 发布于: | 雪球 | 转发：0 | 回复：0 | 喜欢：2 |
已知有如下4张表：
成绩表：score(学号,课程号,成绩)
课程表：course(课程号,课程名称,教师号)
教师表：teacher(教师号,教师姓名)
1.汇总分析
-查询学生的总成绩并进行排名
/*
【知识点】分组查询
分析思路
select 查询结果 [总成绩：sum(成绩), 学号]
from 从哪张表中查找数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [学生的总成绩：按照每个学生学号进行分组]
order by 排序 [按照总成绩进行排序：sum(成绩)];
/*
select 学号 ,sum(成绩) from score
group by 学号
order by sum(成绩) ;
-查询平均成绩大于60分的学生的学号和平均成绩
/*
【知识点】分组+条件
分析思路
select 查询结果 [学号, 平均成绩: avg(成绩)]
from 从哪张表中查找数据 [成绩表score]
where 查询条件 [没有]
group by 分组 [学号]
having 分组条件 [平均成绩大于60分：avg(成绩 ) >60]
order by 排序 [没有];
/*
select 学号 ,avg(成绩) from score
group by 学号
having avg(成绩 ) >60
2.复杂查询
-查询各学生的年龄（精确到月份）
/*
【知识点】时间格式转化
*/
select 学号 ,timestampdiff(month ,出生日期 ,now())/12
from student ;
-查询本月过生日的学生
select *
from student
where month (出生日期 ) = month(now())+2;
.多表查询
-检索"0001"课程分数小于60，按分数降序排列的学生信息
思路如图：
select a.*,b.成绩
from student as a
inner join score as b
on a.学号 =b.学号
where b.成绩 <60 and b.课程号 =01
order by b.成绩 desc;
-查询不同老师所教不同课程平均分从高到低显示
【知识点】分组+条件+排序+多表连接，思路如图
select a.教师号,a.教师姓名,avg(c.成绩)
from teacher as a
inner join course as b
on a.教师号= b.教师号
inner join score c on b.课程号= c.课程号
group by a.教师姓名
order by avg(c.成绩) desc;
-查询课程名称为"数学"，且分数低于60的学生姓名和分数
【知识点】多表连接，思路如图
select a.姓名,b.成绩
from student as a
inner join score as b
on a.学号 =b.学号
inner join course c on b.课程号 =c.课程号
where b.成绩 <60 and c.课程名称 ='数学';
-查询任何一门课程成绩在70分以上的姓名、课程名称和分数（与上题类似）
select a.姓名,c.课程名称 ,b.成绩
from student as a
inner join score as b
on a.学号=b.学号
inner join course c on b.课程号 =c.课程号
where b.成绩 >70;
-查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
【知识点】分组+条件+多表连接
翻译成大白话:计算每个学号不及格分数个数，筛选出大于2个的学号并找出姓名，平均成绩，思路如图：
select b.姓名,avg(a.成绩),a.学号
from score as a
inner join student as b
on a.学号 =b.学号
where a.成绩 <60
group by a.学号
having count(a.学号 ) >=2;
-查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select distinct a.学号 ,a.成绩 ,a.课程号
from score as a
inner join score as b
on a.学号 =b.学号
where a.成绩 =b.成绩 and a.课程号 != b.课程号 ;
-查询课程编号为“0001”的课程比“0002”的课程成绩高的所有学生的学号
【知识点】多表连接+条件，思路如图
select a.学号
from
(select 学号 ,成绩 from score where 课程号=01) as a
inner join
(select 学号 ,成绩 from score where 课程号=02) as b
on a.学号 =b.学号
inner join student c on c.学号 =a.学号
where a.成绩 >b.成绩 ;
-查询学过编号为“0001”的课程并且也学过编号为“0002”的课程的学生的学号、姓名
思路如图
select a.学号
from
(select 学号 ,成绩 from score where 课程号=01) as a
inner join
(select 学号 ,成绩 from score where 课程号=02) as b
on a.学号 =b.学号
inner join student c on c.学号 =a.学号
where a.成绩 >b.成绩 ;
-查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名
思路如图
select s.学号 ,s.姓名,a.学号 ,b.课程号,c.教师号 ,c.教师姓名
from student as s
inner join score as a
on s.学号 =a.学号
inner join course b on a.课程号 =b.课程号
inner join teacher c on b.教师号 = c.教师号
where c.教师姓名 ='孟扎扎';
-查询没学过"孟扎扎"老师讲授的任一门课程的学生姓名 （与上题类似，"没学过"用not in来实现)
select 姓名 ,学号
from student
where 学号 not in (
select a.学号
from student as a
inner join score as b
on a.学号 =b.学号
inner join course as c on b.课程号 =c.课程号
inner join teacher as d on c.教师号 =d.教师号
where d.教师姓名 ='孟扎扎');
-查询没学过“孟扎扎”老师课的学生的学号、姓名（与上题类似）
select 学号, 姓名
from student
where 学号 not in
(select 学号 from score where 课程号=
(select 课程号 from course where 教师号 =
(select 教师号 from teacher where 教师姓名 ='孟扎扎')
)
);
-查询选修“孟扎扎”老师所授课程的学生中成绩最高的学生姓名及其成绩（与上题类似,用成绩排名，用 limit 1得出最高一个）
select a.姓名,b.成绩
from student as a
inner join score as b on a.学号=b.学号
inner join course as c on b.课程号 =c.课程号
inner join teacher as d on c.教师号 = d.教师号
where d.教师姓名 = '孟扎扎'
order by b.成绩 desc limit 1;
-查询至少有一门课与学号为“0001”的学生所学课程相同的学生的学号和姓名
select 学号 ,姓名
from student
where 学号 in
(select distinct(学号) from score where 课程号 in
(select 课程号 from score where 学号=0001))
and 学号 !=0001;
-按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
【知识点】多表连接 新建字段 ，思路如图
select a.学号,avg(a.成绩 ),
max(case when b.课程名称 = '数学' then a.成绩 else null end ) as '数学',
max(case when b.课程名称 = '语文' then a.成绩 else null end ) as '语文',
max(case when b.课程名称 = '英语' then a.成绩 else null end ) as '英语'
from score as a
inner join course as b
on a.课程号 =b.课程号
group by a.学号 ;
4.SQL高级功能
-查询学生平均成绩及其名次
【知识点】窗口函数排名，思路如图
select 学号 ,avg(成绩),
row_number () over( order by avg(成绩) desc)
from score
group by 学号 ;
-按各科成绩进行排序，并显示排名
select 课程号 ,
row_number () over(partition by 课程号 order by 成绩 )
from score ;
-查询每门功成绩最好的前两名学生姓名
【知识点】窗口函数排名+多表连接+条件
select a.课程号 ,b.姓名 ,a.成绩,a.ranking from (
select 课程号 ,学号 ,成绩 ,
row_number () over(partition by 课程号 order by 成绩 desc) as ranking
from score) as a
inner join student as b on a.学号 =b.学号
where a.ranking <3 ;
-查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（与上一题相似）
select b.姓名 ,a.课程号 ,a.成绩
from (
select 课程号 ,学号 ,成绩 ,
row_number () over( partition by 课程号 order by 成绩 desc) as ranking
from score ) as a
inner join student as b
on a.学号 =b.学号
where a.ranking in( 2,3) ;
-查询各科成绩前三名的记录（不考虑成绩并列情况）（与上一题相似）
select b.姓名 ,a.课程号 ,a.成绩
from (
select 课程号 ,学号 ,成绩 ,
row_number () over( partition by 课程号 order by 成绩 desc) as 'ranking'
from score ) as a
inner join student as b
on a.学号 =b.学号
where a.ranking <4 ;
这些题要融会贯通，以后碰到类似的面试题都可以找到对应场景的使用案例。
推荐： 如何从零学会sql？
