MySQL经典50题-第41到45题
2021-04-11 本文已影响0人
皮皮大
MySQL50-11-第41-45题
本文中介绍的是第41-45题，主要包含的知识点：
- 表的自连接查询比较信息
- 找出前2名
- 分组排序+having 过滤
5个题目是
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
- 每门功课最好的前2名
- 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
- 检索至少选修两门课程的学生学号
- 查询选修了全部课程的学生信息
image
题目41
题目需求
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
分析过程
- 课程成绩：Score，s_score
- 学生编号：Score，s_id
- 课程编号：Score，c_id
3个字段同时在一个表中，所以我们可以通过一个表Score的自连接来实现查出
SQL实现
select
a.s_id
,a.c_id
,a.s_score
from Score a
join Score b
on a.c_id != b.c_id
and a.s_score = b.s_score
and a.s_id != b.s_id;
image-20201122001411784
我们对学号还需要去重下：
select
distinct a.s_id
,a.c_id
,a.s_score
from Score a
join Score b
on a.c_id != b.c_id
and a.s_score = b.s_score
and a.s_id != b.s_id;
image
再看看原始的数据中是否符合要求：
image
题目42
题目需求
查询每门功成绩最好的前两名
分析过程
题目的要求就是找出每门课的前2名同学
- 成绩：Score
- 学科：Course
SQL实现
自己的方法
还需要好好优化的😭
-- 先找出语文的前2名同学
select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '语文' -- 改成数学和英语即可求出相应的信息
order by sc.s_score desc
limit 2;
image
将3门学科的信息进行拼接即可求出答案：
-- 最终脚本
(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '语文'
order by sc.s_score desc
limit 2)
union
(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '数学'
order by sc.s_score desc
limit 2)
union
(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '英语'
order by sc.s_score desc
limit 2)
image
参考方法（好方法）
如何解决前几名排序的问题🐂🍺🚗太牛了
select
a.c_id
,a.s_id
,a.s_score
from Score a
where (select count(1) -- count(1)类似count(*)：统计表b中分数大的数量
from Score b
where b.c_id=a.c_id -- 课程相同
and b.s_score >= a.s_score) <= 2 -- 前2名
order by a.c_id;
image
首先我们看看真实的数据，我们以01课程来进行解释上面的代码：
image
符合要求count(1)<=2的只有两种情况
image
还需要好好理解下😭
题目43
image
题目需求
统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
分析过程
课程号：Score，c_id
学生：Score，s_id
SQL实现
select
c_id
,count(s_score) num
from Score
group by c_id
having num > 5
order by num desc, c_id;
image
题目44
题目需求
检索至少选修两门课程的学生学号
分析过程
课程：Score，c_id
学号：Score，s_id
SQL实现
结果显示全部满足要求
select
s_id
,count(*) num
from Score
group by s_id
having num >= 2;
image
题目45
题目需求
查询选修了全部课程的学生信息
分析过程
- 首先我们在Course表看看全部课程数目是多少(num)
- 然后在Score表中找出课程是num的学生信息
SQL实现
自己的方法
1、全部的课程数目num
select count(*) from Course; -- 总共3门
image-20201122095253954
2、从Score表分组统计每个人的课程数目，满足是3的学生信息
select
s_id
,count(c_id) num -- 课程数目
from Score
group by s_id
having num in (select count(*)
from Course); --满足全部课程
image
3、我们找出上面结果中的学生信息即可
select
s.*
,count(c_id) num -- 课程数目
from Score sc
join Student s
on sc.s_id = s.s_id
group by s.s_id
having num in (select count(*)
from Course); --满足全部课程
image
参考方法
select * -- 3、s_id对应的学生信息
from Student
where s_id in(select s_id -- 2、最大课程数对应的s_id
from Score
group by s_id
having count(*)=(select count(*) from Course) -- 1、全部课程数
)
