Fork me on GitHub

MySQL50-5-第11-15题

MySQL50-5-第11-15题

本文中介绍的是第11-15题,具体的题目包含:

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

题目11

题目需求

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

分析过程

课程:Course

学生:Student

SQL实现

1
2
3
4
5
6
7
8
9
-- 自己的方法
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)作为课程的总数:

1
2
3
4
5
6
7
8
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)
);

1
2
3
4
5
6
7
8
9
10
-- 方法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号同学没有任何成绩,不符合

具体实现过程为:

1
2
3
4
5
6
7
8
9
10
11
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);

1
2
3
4
5
6
7
-- 方法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同学学习的课程完全相同的同学的信息

分析过程

本题我们可以$\color{red}{投机}$:因为总课程数3,而01号同学的课程数刚好是3,所以我们只要找出在Score表中课程也修满3门的同学即可

SQL实现

  1. 自己的方法
1
2
3
4
5
6
7
8
9
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)来代替:

1
2
3
4
5
6
7
8
9
10
11
12
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 本身排除
);

  1. 使用group_concat函数

group_concat的使用方法为:

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

我们将Score表中每个s_idc_id进行分组合并,实际的效果如下:

1
2
3
4
5
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号同学相同的结果即可,取出学号:

1
2
3
4
5
6
7
8
9
10
11
12
13
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实现

自己的方法,具体过程如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
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='张三')
));

1
2
3
4
5
6
7
8
9
10
-- 修过张老师课程的学生的学号
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实现

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

1
2
3
4
5
6
7
select
s_id
,round(avg(s_score)) avg_score
from Score
where s_score < 60
group by s_id
having count(s_score) >= 2; -- 2门课程大于等于60分的数量

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 自己的方法
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 参考方法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门都没有成绩,这个本题需要改进的地方。

本文标题:MySQL50-5-第11-15题

发布时间:2020年11月09日 - 00:11

原始链接:http://www.renpeter.cn/2020/11/09/MySQL50-5-%E7%AC%AC11-15%E9%A2%98.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

Coffee or Tea