MySQL50题_第11到15题

公众号：尤而小屋
作者：Peter
编辑：Peter

大家好，我是Peter~

继续MySQL经典50题的第11-15题，具体的题目如下：

• 查询没有学完全部课程的同学的信息
• 查询至少有一门课与学号为01的同学所学相同的同学的信息
• 查询和01同学学习的课程完全相同的同学的信息
• 查询没有修过张三老师讲授的任何一门课程的学生姓名
• 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

题目11

题目需求

查询没有学完全部课程的同学的信息

分析过程

题目中主要涉及到的是：课程Course表和学生Student表

SQL实现

自己方法实现的思路就是：根据学号分组统计课程数，然后将对应学号的学生进行排除

-- 自己的方法
select *   -- 排除学号后得到的结果
from Student 
where s_id not in (
  select s_id 
  from(select 
         s_id
         ,count(s_id) as number  -- 3.最大课程数所在的学号需要排除
       from Score 
       group by s_id) s  -- 取别名
  where number=(select max(number)   -- 2.保证最大的课程数  
                from(select 
                     s_id, 
                     count(s_id) as number  -- 1.学号和课程个数统计（即修了几门课）
                   from Score 
                   group by s_id)t));  -- 别名

自己的方法一开始在课程的最大数中没有使用Course表，导致多使用了一个临时表的结果，现在改成使用Course表的统计值，即结果3门课，作为课程的总数：

select s.* 
from Student s
where s_id not in (
  select s_id 
  from Score s1
  group by s_id 
  having count(*) = (select count(*) from Course)
);

-- 方法2：having

select s.* 
from Student s  -- 学生表
left join Score s1  -- 成绩表
on s1.s_id = s.s_id
group by s.s_id  -- 学号分组
having count(s1.c_id) < (  -- 分组后学生的课程数<3
  select count(*) from Course  -- 全部课程数=3
)

题目12

题目需求

查询至少有一门课与学号为01的同学所学相同的同学的信息

分析过程

题目的意思就是至少有一门课程和01同学的所学课程相同

课程：Score——>c_id

学生：Student——>s_id

SQL实现

首先看看结果的：因为01号同学修了全部课程，所以其他的同学都是满足要求，除了08号同学没有任何成绩，不符合。

具体实现过程为：

select *   -- 3、求出学生信息
from Student 
where s_id in (
  select distinct s_id   -- 2、找出满足课程在01学生课程中的全部学号（学生），学号去重，同时将01自己排除
  from Score 
  where c_id in (
    select c_id 
    from Score 
    where s_id=01   -- 1、找出学号01同学的全部课程
                ) 
  and s_id != 01);

-- 方法2
select s1.*
from Student s1
join Score s2
on s1.s_id = s2.s_id  -- 学生表和成绩表的关联
and c_id in (select c_id from Score where s_id=01)  -- 对课程进行限制，只在01学生的课程内
group by s1.s_id;  -- 根据学号分组

题目13

题目需求

查询和01同学学习的课程完全相同的同学的信息

分析过程

本题我们可以投机：因为总课程数3，而01号同学的课程数刚好是3，所以我们只要找出在Score表中课程也修满3门的同学即可。

SQL实现

1. 自己的方法

select * 
from Student 
where s_id in (select s_id   -- 3、步骤2中得到的学号是满足要求的
               from(select distinct(s_id), count(c_id) number 
                    from Score 
                    group by s_id)t1 -- 1、学号和所修课程分组的结果t1
               where number=3  -- 2、投机：选择出所修课程数是3（01修了3门）的学号
               and s_id !=01  -- 01 本身排除
              );             

我们在上面的步骤2中不考虑直接指定3（where number=3），而是用01学生所修的课程数（虽然也是3）来代替：

select * 
from Student 
where s_id in(
  select s_id   -- 3、步骤2中得到的学号是满足要求的
  from(select distinct(s_id), count(c_id) number 
       from Score 
       group by s_id)t1 -- 1、学号和所修课程分组的结果t1
       where number=(select count(c_id) number 
                     from Score 
                     group by s_id having s_id=01)  -- 2、改变的地方：使用学号01的课程数3来代替
  and s_id !=01  -- 01 本身排除
);             

2. 使用group_concat函数

group_concat的使用方法为：

group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

我们将Score表中每个s_id的c_id进行分组合并，实际的效果如下：

select 
	s_id
	,group_concat(c_id order by c_id) -- 分组合并，同时排序
from Score 
group by s_id;

需要进行排序的原因是防止出现这种情况：01同学修的课程顺序是：01，02，03；如果有同学修课的顺序是02，03，01，虽然顺序不同，但是本质上他们修的课程是相同的

使用排序后都会变成01，02，03，保证结果相同

那么之后，我们只需要判断合并后和01号同学相同的结果即可，取出学号：

select *   -- 3、查询信息
from Student
where s_id in(
  select s_id 
  from Score 
  group by s_id 
  having group_concat(c_id order by c_id)=(   -- 2、找出和01号学生分组合并结果相同的学号s_id；也需要排序
    select group_concat(c_id order by c_id)   -- 1、找出01号学生分组合并的结果，同时排序；排序很重要
    from Score 
    group by s_id
    having s_id=01)
  and s_id != 01  -- 将自身排除
);

题目14

题目需求

查询没有修过张三老师讲授的任何一门课程的学生姓名

分析过程

老师：Teacher——>t_name(t_id)

课程：Course——>t_id——>c_id

姓名：Student

SQL实现

自己的方法，具体过程如下：

select s_name   -- 4、学号取反找到学生姓名
from Student 
where s_id not in(
  select distinct(s_id) -- 3、课程号找到对应的学号
  from Score 
  where c_id=(
    select c_id -- 2、教师编号找到对应的课程号
    from Course 
    where t_id=(
      select t_id   -- 1、姓名找到教师编号
      from Teacher 
      where t_name='张三')
  ));

 -- 修过张老师课程的学生的学号
 select distinct(s_id) 
 from Score 
 where c_id=(
   select c_id 
   from Course 
   where t_id=(
     select t_id 
     from Teacher 
     where t_name='张三')); -- 修过张三老师课的学生

题目15

题目需求

查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

分析过程

我们需要统计每个学生不及格的课程的数量：通过Score表中的数据直接统计c_id小于等于60的数量；然后再和Student表进行联结

SQL实现

首先看看哪些同学是满足两门或者两门以上是及格的

-- 2门及以上不及格的

select 
	s_id
  ,round(avg(s_score))  avg_score
from Score 
where s_score < 60 -- 小于60分，不及格
group by s_id
having count(s_score) >= 2;   -- 不及格的有2门以上

说明04，06是我们最终想要的结果

-- 自己的方法
select 
	s.s_id
	,s.s_name 
	,t.avg_score
from Student s
join (select 
      	s_id
      	,round(avg(s_score))  avg_score
      from Score 
      where s_score < 60 
      group by s_id
      having count(s_score) >= 2)t
on s.s_id=t.s_id

-- 参考方法1

select 
	a.s_id,
	a.s_name,
	ROUND(AVG(b.s_score)) 
from Student a 
left join Score b 
on a.s_id = b.s_id
where a.s_id in(
  select s_id 
  from Score 
  where s_score<60 
  group by s_id 
  having count(1)>=2)
group by a.s_id,a.s_name

-- 参考方法2
select 
	s.s_id
	,s_name
	,round(avg(s_score), 2) avg_score
from Student s
join Score sc
on s.s_id=sc.s_id
and sc.s_score < 60  -- 不及格
group by s.s_id   -- 学号分组
having count(sc.c_id )>= 2;   -- 2门课	

改进点

上面的两种方法都没有考虑都08学生，3门都没有成绩，这个本题需要改进的地方。