3.26sql面试题2

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）：
大白话就是1课的成绩比2课的成绩好不就完了吗？这里我当时想到的是1号课的学生我搞一张表，2号课的学生我也搞一张表，两张表inner join一下再一对比，over！

我：
select a.s_id from (select s_id,s_s_score from score where c_id = 1) a
join (select s_id,s_s_score from score where c_id = 2) b
on a.s_id =b.s_id
where a.s_s_score >b.s_s_score;
如下图
参考答案：

⚠️作者这里是查询了学生信息和两门课的成绩，所以是加了student表，做3表连接，因为题目是只要id嘛所以我没做3表，算正确吧☑️

总结：表内比较，拆表作比较！

2.查询平均成绩大于60分的学生的学号和平均成绩（重点）：
大白话就是……这个不用翻译的，非要翻译的话就是每个人的平均分大于60分的id和均分，或者每个人算算均分，筛选掉均分小于60分的

我：
select s_id,avg(s_s_score) from score group by s_id having avg(s_s_score)>60;

参考答案：

总结：平均分的事，那都不是事（我没想好这算啥）

3.查询所有成绩都小于60分的学生的学号、姓名：
大白话就是都不及格嘛，都不及格是什么意思？1课小于60，2课小于60……，100门课叫我怎么办？这里我当然知道只有3门课，我想了半天，峰回路转发现只要有1门课大于60分就不算在内，not in就派上用场了！

我：select id,name from student where id not in
(select s_id from score where s_s_score > 60);

参考答案：

⚠️很多题目都这样，题目要求只是id但作者都是加上了其他信息，所以不要说我偷懒，是作者太勤快了！

总结：比较所有的，找个特别的！

4.⚠️⚠️查询没学过“张三”老师课的学生的学号、姓名（重点）：
这题说实话我想了很久，就想省点事，但一定要理清思路，思路就是张三老师的课是几号课？成绩单中谁没有这门课的成绩？

我
方法1
select id,name from student where id not in (
select distinct s_id from score where c_id = (
select c.c_id from teacher te inner join course c on c.t_id = te.t_id where t_name = ‘张三’
)
);

方法2:
select * from student where id not in (
select s_id from score s
inner join course c on s.c_id = c.c_id
inner join teacher te on te.t_id = c.t_id
where te.t_name = ‘张三’
);

参考答案：

⚠️这题我是先想到方法1，觉得嵌套里面还要嵌套太过麻烦也不美观，但思路就是那样，改进后也只是把嵌套里面的改成了inner join，不知道这两个谁更好一点，有没有能指导下的！🙋‍♂️

总结：跨表的关系，一条一条理

5.查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
大白话既要去1号课教室的又要去2号的教室的站在门口不知道去哪里，两张表的交集，这里还要个name所以是3表查询

我：
select st.id,st.name from student st
inner join (select s_id from score where c_id = 1) a on a.s_id = st.id
inner join (select s_id from score where c_id = 2) b on b.s_id = st.id;

参考答案：

总结：表内作比较，拆表做比较

6.查询学过编号为“01”的课程但没学过编号为“02”的课程的学生的学号、姓名（重点）：
大白话就是，1号表和2号表相交后1号表独有的那部分门，这个用left join，首先left join是指主表排排队，从表来站队，从表的null就是主表的独

我：
select id,name from student where id =
(
select a.s_id
from (select s_id from score where c_id = 1) a
left join (select s_id from score where c_id = 2) b
on b.s_id = a.s_id where b.s_id is null
);

参考答案

总结：干过b没干过b？主表从表选天秀

7.查询没有学全所有课的学生的学号、姓名(重点)
大白话就是学的课程数量不够呗，总课程数有课程表，学的课程数有成绩表，这里还要个name所以3表连接；

我：
select id,name from student st
inner join(
select s_id from score
group by s_id
having count() < (select count() from course)
) jg
on jg.s_id = st.id;

参考答案：

⚠️作者这里是我给成绩表按照id分个组看看大家都学了几门课，对比一下总课数，只要小于总课数的就是了，这里需要注意⚠️分组group by需要2个字段，id分组了名字也要分组，我个人觉得作者的更美观流畅吧，尤其是group by2个字段，感觉很厉害，而且还有distinct去重，但如果course表的id是主键，那就本身就去重了，虽然老师说去重无罪！

总结：这个没想好，谁帮我总结下吧

8.查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）：
大白话就是所学的课程号在1号学生的课程号里，这个比较简单，需要去重一下，因为很有可能学生选修的课程和1号学生多个一样，比如2号学生和1号学生一样都选了语数外，但我只要2号学生1次id即可

我：
select distinct id,name
from student st
inner join score s
on s.s_id = st.id
where s.c_id in (
select c_id from score where s_id = 1
)
and id != 1;

参考答案：

总结：这个比较简单

9.⚠️⚠️⚠️查询和“01”号同学所学课程完全相同的其他同学的学号(重点)：
大白话就是我的课号必须完全和你一样，这个我没做出来，怎么都不能完全匹配，因为这里是正好1号学生学全了3门课，所以做出来的结果是对的，但如果总课数比1号同学学的多的话就不对了，我记得老师讲过any|all之类的，但我当时没做笔记，实在也找不到，这个我还要继续查一下

就不贴了，贴作者的参考答案吧

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名 和47题一样（重点，能做出来）
这个和之前的没学过张三老师课那题一样，不赘述了

11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）
我的思路是先查出来2门课不及格的学生号，再算平均分，两门课不及格就是count（*）<60

我的：
select id,name,avg(s_s_score) from student st inner join score s on st.id=s.s_id group by id having id in (
select s_id from score where s_s_score < 60 group by s_id having count(*) >=2
);

参考答案：

12.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)⚠️⚠️⚠️：
这个我也觉得难，太绕了，而且要显示的实在太多，并且我理解错了，最后参考答的时候才发现理解错了，所以算做错了
我一开始的理解是

参考答案：

再一看有点行转列的意思，行转列我还不会，笨人有笨方法我试了下麻烦点的

select distinct s.s_id,a.s_s_score 数学,b.s_s_score 语文,c.s_s_score 英语,d.均分 均分
from score s
left join (select s_id,s_s_score,c_id = 1 数学 from score where c_id = 1) a on s.s_id =a.s_id
left join (select s_id,s_s_score,c_id = 2 语文 from score where c_id = 2) b on s.s_id =b.s_id
left join (select s_id,s_s_score,c_id = 3 英语 from score where c_id = 3) c on s.s_id =c.s_id
left join (select s_id,avg(s_s_score) 均分 from score group by s_id) d on s.s_id =d.s_id
order by 均分 desc;

我放弃治疗了！

13.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）
我首先想到的是limit，排序再找top，但很麻烦
select a.id,a.name from (
select id,name,c_id,s_s_score,
row_number() over(partition by c_id order by s_s_score desc) 排序
from student st inner join score s on id=s_id order by c_id,s_s_score desc) a
where a.排序 in(2,3);

参考：

13.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)
#先分段
我的：
select c.c_id,c.c_name,
sum(case when s_s_score>=85 then 1 else 0 end) ‘[85-100]’,
sum(case when s_s_score>=70 and s_s_score<85 then 1 else 0 end) ‘[70-85]’,
sum(case when s_s_score>=60 and s_s_score<70 then 1 else 0 end) ‘[60-70]’,
sum(case when s_s_score<60 then 1 else 0 end) ‘[<60]’
from score s
inner join course c
on c.c_id=s.c_id
group by c_id;

参考：

14.查询学生平均成绩及其名次（重点）
我的：
select a.s_id,a.jf,row_number() over(order by jf desc) 排序
from
(select s_id,avg(s_s_score) jf from score group by s_id order by jf desc) a ;

参考：

……刚刚我突然思绪飘到不知道哪里了，回过神来没心情写了，今天就到这里吧，下午一点多写到现在5点了。

稍微总结下就是理清思路，在连接的评论里还有很多大神用到了变量之类的，变量在上课的时候我直接跳过了，所以还要回去再摸索了，很多答案肯定不止一个，只是想没想到和哪个更优，什么叫优？这个需要钻研，今天没完成任务因为耗时太过了，我还是无业游民呢想想就难过。等我振作起来再写！

祝大家sql越用越好！