笔者使用的 MySQL 版本 是 MySQL 5.7.28
。
查询学过01课程,但是没有学过02课程的学生信息(注意和上面?题目的区别)
首先看看哪些同学是满足要求的:只有06号同学是满足的
直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08
select s1.*
from Student s1
where s_id not in ( -- 直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08
select s2.s_id from Score s2
join Score s3
on s2.s_id=s3.s_id
where s2.c_id='01' and s3.c_id ='02'
);
将上面题目中的02课程直接取反,导致同时修过01,02,03或者只修01,03的同学也会出现
select s1.*
from Student s1
where s_id in (
select s2.s_id from Score s2
join Score s3
on s2.s_id=s3.s_id
where s2.c_id='01' and s3.c_id !='02' -- 直接取反是不行的,因为修改(01,02,03)的同学也会出现
);
https://www.jianshu.com/p/9abffdd334fa
-- 方法1:根据两种修课情况来判断
select s1.*
from Student s1
where s1.s_id in (select s_id from Score where c_id='01') -- 修过01课程,要保留
and s1.s_id not in (select s_id from Score where c_id='02'); -- 哪些人修过02,需要排除
!!!!!方法2:先把06号学生找出来
select * from Student where s_id in (
select s_id
from Score
where c_id='01' -- 修过01课程的学号
and s_id not in (select s_id -- 同时学号不能在修过02课程中出现
from Score
where c_id='02')
);