sql统计各科成绩⼤于平均分的⼈_数据分析师SQL⾯试必备50
题
以下是SQL⾯试必备的经典的50道题⽬，每道题都有博主本⼈的解题思路和对应的SQL语句。
每道题的思路与答案均为博主本⼈主观理解，仅供参考。
环境：MySQL8.0
可视化⼯具：Navicat
1、查询课程编号为01的课程⽐02的课程⾼的所有学⽣的学号和成绩
解题思路：
(1) 先把课程为01的学号和成绩出来 as 表a
(2) 再把课程为02的学号和成绩出来 as 表b
(3) ⽤inner join将表a和表b按照s_id连接起来
(4) 最后⽤where筛选表a成绩⼤于表b成绩的学⽣编号
select a.s_id
from
(select s_id,s_score from score where c_id='01') as a
inner join
(select s_id,s_score from score where c_id='02') as b on a.s_id=b.s_id
where a.s_score>b.s_score;
2、查询平均成绩⼤于60分的学⽣的学号和平均成绩
解题思路：
颜名称童之宝(1) 先⽤group by对s_id进⾏分组
(2) 再⽤having过滤平均分⼤于60
tips：group by⾥的东西必须是select⾥的东西，除⾮是统计函数(avg,max等)log4j日志输出到指定文件
select s_id,avg(s_score)
from score
group by s_id
having avg(s_score)>60;
3、查询所有学⽣的学号、姓名、选课数、总成绩
解题思路：
(1) 姓名在student表，成绩在score表，因此需要连接两表；student表左连接score表，这样才能保证保留所有学⽣的信息
(2) 按s_id和s_score进⾏分组
(3) 选课数使⽤count()，总成绩使⽤sum(if(...))
a.s_id,
a.s_name,
count(b.c_id),
sum(if(b.s_score is null,0,b.s_score))
from student as a
left join score as b on a.s_id=b.s_id
group by a.s_id,a.s_name;
4、查询姓“猴”的⽼师的⼈数
解题思路：
(1) 使⽤like和%进⾏模糊查询
(2) ⼈数使⽤count()函数
select count(t_id)switchcase多条语句
from teacher
where t_name like '猴%';
5、查询没学过“张三”⽼师课的学⽣的学号、姓名
解题思路：
(1) 先出学过"张三"⽼师可的学⽣，这些学⽣以外的学⽣就是没学过"张三"⽼师课的
(2) 在teacher表中获取"张三"的t_id，在course表中获取所有⽼师t_id和课程c_id，在score表中获取学⽣S_id和课程c_id
(3) teacher表与course表按t_id内连接，course表与score表按s_id内连接，然后选出学⽣s_id
(4) 最后从student表中中过滤不在上⾯的学⽣s_id
select s_id,s_name
from student
where s_id not in(
select c.s_id
from
(select t_id from teacher where t_name='张三') as a
inner join
(select t_id, c_id from course) as b on a.t_id=b.t_id
inner join
(select s_id, c_id from score) as c on b.c_id=c.c_id
);
6、查询学过“张三”⽼师所教的所有课的同学的学号和姓名
解题思路：
(2) 接着查学过以上课程的学⽣：将student表和score表按s_id内连接
(3) 最后对s_id进⾏group by，过滤所选课程数量等于"张三"所教课程数量
select a.s_id,a.s_name
from student as a
join score as b on a.s_id=b.s_id
where b.c_id in ( -- (2)接着查学过以上课程的学⽣：将student表和score表按s_id内连接-- (1)⾸先查"张三"教的所有课程c_id：将teahcer表和course表按t_id内连接
select b.c_id
from teacher as a
join course as b on a.t_id=b.t_id
where a.t_name='张三'
)
-- (3)最后对s_id进⾏group by，过滤所选课程数量等于"张三"所教课程数量
group by a.s_id
having count(b.c_id)=(
select count(b.c_id)
from teacher as a
join course as b on a.t_id=b.t_id
where a.t_name='张三'
spingmvc的面试题
);
7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学⽣的学号和姓名
解题思路：
(1) 查出学过编号01课程学⽣s_id作为表a
(2) 查学过编号02课程学⽣s_id作为表b
(3) 将表a和表b内连接，选出学⽣编号s_id
(4) 最后从student表筛选学⽣编号包含以上s_id的学⽣
select s_id,s_name
from student
where s_id in (
select a.s_id
from
(select s_id from score where c_id='01') as a
join
);
8、查询课程编号为“02”的总成绩
select sum(s_score)
from score
group by c_id
having c_id='02';
9、查询所有课程成绩⼩于60分的学⽣的学号和姓名
解题思路：
(1) 将score表按s_id进⾏group by，过滤条件为课程最⼤成绩⼩于60(如果最⼤成绩都⼩于60，那就意味所有成绩都⼩于60)
(2) 从student表中选出学⽣编号包含以上s_id的学⽣
select s_id,s_name
from student
where s_id in (
select s_id
from score
group by s_id
having max(s_score)<60
);
10、查询没有学全所有课的学⽣的学号和姓名
解题思路：
(1) ⾸先从course表获取所有课程的总数
(2) 接着student表左连接score表并按student.s_id进⾏group by，过滤条件是每个学⽣的课程数量⼩于第⼀步的课程总数
(3) 最后从student表中筛选出学⽣编号包含以上s_id的学⽣
tips：可能存在⼀门课也没有学的学⽣
select a.s_id,a.s_name
from
student as a
left join score as b on a.s_id=b.s_id
group by a.s_id
mysql面试题目及答案having count(b.c_id)
select count(c_id) from course
);
11、查询⾄少有⼀门课与学号为“01”的学⽣所学课程相同的学⽣的学号和姓名
(1) 查出学号为"01"的学⽣所学课程编号c_id
(2) student表左连接score表，选出c_id有包含上⾯c_id的学⽣
(3) 接着对s_id进⾏group by，过滤s_id!=1，最后选择学⽣编号和姓名
select a.s_id,a.s_name
from
student as a
left join score as b on a.s_id=b.s_id
where b.c_id in (
select c_id from score where s_id='01'
web制作网页登录界面)
group by a.s_id
having a.s_id<>'01';
12.查询和“01”号同学所学课程完全相同的其他同学的学号
解题思路：
(1) 对score表按s_id进⾏group by，对每个s_id的c_id进⾏group_concat转换，结果作为表a
(2) 查出"01"同学所学课程编号c_id，并进⾏group_concat转换，结果作为表b，将表a与表b按照group_concat的结果进⾏内连接
(3) 以上结果就是与"01"同学课程完全相同的同学(包括"01")，最后筛选学⽣编号不为"01"的
select a.s_id
from
(select s_id,group_concat(c_id order by c_id separator ',') as c_id_str
from score
group by s_id) as a
inner join
(select group_concat(c_id order by c_id separator ',') as c_id_str
from score
where s_id='01') as b on a.c_id_str=b.c_id_str
where a.s_id<>'01';
13、查询没学过“张三”⽼师讲授的任⼀门课程的学⽣姓名
解题思路：
(1) 查出"张三"的教师编号t_id as表a
(2) 表a按t_id内连接course表，course表再按c_id内连接score表，便选出学过"张三"课的学⽣编号s_id
(3) 最后student表中选出不包含上⾯编号的学⽣
select s_name