MySQL经典50题-第41到45题

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

题目需求

查询选修了全部课程的学生信息

分析过程

1. 首先我们在Course表看看全部课程数目是多少(num)
2. 然后在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、全部课程数
             )