相关表数据:
- Score_info表
2、Student_info表
3、Course_info表
4、Teacher_info 表
题目及思路解析:
多表连接
6、查询学过 “李体音”老师所教的所有课的同学的学号、姓名
注意:是学过 所有 李体音老师的课程 的学生
代码1:
select
sc.stu_id,
stu_name
from score_info sc
left join student_info si on sc.stu_id = si.stu_id
join (
select
course_id
from course_info
where tea_id=(
select
tea_id
from teacher_info
where tea_name='李体音'))t1
on sc.course_id=t1.course_id
group by sc.stu_id, stu_name
having count(*)=2;
代码2:
select
t1.stu_id,
si.stu_name
from
(
select
stu_id
from score_info si
where course_id in
(
select
course_id
from course_info c
join teacher_info t
on c.tea_id = t.tea_id
where tea_name='李体音' --李体音教的所有课程
)
group by stu_id
having count(*)=2 --学习所有课程的学生
)t1
left join student_info si
on t1.stu_id=si.stu_id;
思路分析:
这里关键在于所有课程
代码1与代码2区别,代码2少join
主要思路是先求出李老师的名字,求出tea_id,接着求出其所教全部课程,然后筛选出选修全部课程的学生,最后显示结果
在大数据场景下,选择代码2比较好一些(Join少),但是代码1和2在筛选选修全部课程时候,条件都是count(*)=2,但是在课程数比较多,或者不易直接获取课程数的时候就不太方便了
代码3:
select
t1.stu_id,
stu_name
from (
select
stu_id,
count(*)
from score_info sc
join course_info ci on sc.course_id = ci.course_id
join teacher_info ti on ci.tea_id = ti.tea_id
where tea_name='李体音'
group by stu_id
)t1
join
(select
count(*)
from course_info ci
join teacher_info ti
on ci.tea_id=ti.tea_id
where tea_name='李体音'
)t2
join student_info st
on t1.stu_id=st.stu_id;
思路分析:
求出学生选修李老师所教课程的数量,再求出李老师教所有课程的数量
1、求出学生选修李老师所教课程的数
用count(*)取出非null值,即上过课程的数量连接course表得到teaid,进而连接teacher表得到教师姓名并进行筛选
2、求出李老师教所有课程的数量
从course表连接teacher表得到对应老师所教的总课程数量,注:只有一行数据
最后直接进行join连接,笛卡尔集
代码3虽然解决上面的筛选条件问题,但是它的代码略显冗余,
因此根据实际需求情况选择
结果:
7、查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名
注意:关键字,学过,任意一门
代码1:
select
distinct sc.stu_id,
stu_name
from score_info sc
left join student_info si on sc.stu_id = si.stu_id
join (
select
course_id
from course_info
where tea_id=(
select
tea_id
from teacher_info
where tea_name='李体音'))t1
on sc.course_id=t1.course_id;
代码2:
select
t1.stu_id,
si.stu_name
from
(
select
stu_id
from score_info si
where course_id in
(
select
course_id
from course_info c
join teacher_info t
on c.tea_id = t.tea_id
where tea_name='李体音'
)
group by stu_id
)t1
left join student_info si on t1.stu_id=si.stu_id;
思路分析:
这里代码1与代码2区别在于,course_id连接,代码1用join,而代码2则是用in
主要思路,先通过教师名字得到教师id,接着根据教师id得到课程id,最后显示结果
注意:第二层查询这里通过分组进行去重(同时选修多门课程)
结果:
8、查询没学过"李体音"老师讲授的任一门课程的学生姓名
注意关键字:没学过,任意一门
代码:
select
stu_id,
stu_name
from student_info
where stu_id not in(
select
stu_id
from score_info
where course_id in
( select
course_id
from course_info
where tea_id=(
select
tea_id
from teacher_info
where tea_name='李体音')));
思路分析:
这道题其实跟上面的题目差不多,在上一题的基础上,使用not in 就可以得到结果
不过,注意的是,not in的是stu_id,有同学可能会想直接在第三层子查询中直接改为Not in就好
但实际上,这样是不行的,因为这样只要学生有选修其他课程,那就会被选中
结果: