MySQL经典50-第11到15题-1

MySQL50-5-第11-15题

本文中介绍的是第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  -- 将自身排除
);