MySQL50-5-第11-15题
本文中介绍的是第11-15题，具体的题目包含：
查询没有学完全部课程的同学的信息
查询至少有一门课与学号为01的同学所学相同的同学的信息
查询和01同学学习的课程完全相同的同学的信息
查询没有修过张三老师讲授的任何一门课程的学生姓名
查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
image
题目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)); -- 别名
image
自己的方法一开始在课程的最大数中没有使用Course表，导致多使用了一个临时表的结果，现在改成使用Course表的统计值
作为课程的总数：
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)
);
image
-- 方法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
image
题目需求
查询至少有一门课与学号为01的同学所学相同的同学的信息
分析过程
题目的意思就是至少有一门课程和01同学的所学课程相同
课程：Score——>c_id
学生：Student——>s_id
SQL实现
首先看看结果的：因为01号同学修了全部课程，所以其他的同学都是满足要求，除了08号同学没有任何成绩，不符合。
image
具体实现过程为：
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);
image
-- 方法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; -- 根据学号分组
image
题目13
题目需求
查询和01同学学习的课程完全相同的同学的信息
分析过程
本题我们可以
：因为总课程数3，而01号同学的课程数刚好是3，所以我们只要找出在Score表中课程也修满3门的同学即可
SQL实现
自己的方法
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 本身排除
);
image
image
我们在上面的步骤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 本身排除
);
image
使用group_concat函数
group_concat的使用方法为：
group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
select
s_id
,group_concat(c_id order by c_id) -- 分组合并，同时排序
from Score
group by s_id;
image
我们将Score表中每个s_id的c_id进行分组合并，实际的效果如下：
image
需要进行排序的原因是防止出现这种情况：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 -- 将自身排除
);
image
题目14
image
题目需求
查询没有修过张三老师讲授的任何一门课程的学生姓名
分析过程
老师：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='张三')
));
image
-- 修过张老师课程的学生的学号
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='张三')); -- 修过张三老师课的学生
image
题目15
题目需求
查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
分析过程
我们需要统计每个学生不及格的课程的数量：通过Score表中的数据直接统计c_id小于等于60的数量；然后再和Student表进行联结
SQL实现
首先看看哪些同学是满足两门或者两门以上是及格的
image
-- 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是我们最终想要的结果
image
-- 自己的方法
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
image
-- 参考方法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门都没有成绩，这个本题需要改进的地方。
扫一扫
356
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
