3、查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
(我想吐槽下这个出题的大神……这题啥意思啊,什么叫可能不存在“02”课程,而且cid列如果为null那么逻辑上是不是对应的score也是null,那么SC 表中此行是不是就没有意义,sid与cid不应设置组合住建约束吗?!)
题目理解:查询存在‘01’课程但不存在‘02’课程的情况
解法1:
select S.sid, S.sname, SC.cid, SC.score
from student AS S left join SC
ON S.sid=SC.sid
where '01' in (select cid from SC where SC.sid=S.sid)
AND '02' not in (select cid from SC where SC.sid=S.sid);
程序运行结果:
sid | sname | cid | score
------------+------------------------+------------+-------
06 | 吴兰 | 01 | 31
06 | 吴兰 | 03 | 34
(2 行记录)
解法2:
select S.sid ,S.sname, SC1.cid, SC1.score
from student AS S
inner join (
select * from SC where cid='01'
)AS SC1
ON S.sid=SC1.sid
where S.sid not in (select sid
from SC
WHERE cid='02');
程序运行结果:
sid | sname | cid | score
------------+------------------------+------------+-------
06 | 吴兰 | 01 | 31
(1 行记录)
解法3:网络答案
SELECT C1.sid, C3.sname, C3.sage, C3.ssex, C1.score AS score1,
COALESCE(CAST(C2.score AS VARCHAR(4)), 'NULL') AS score2
FROM (SELECT * FROM SC WHERE Cid = '01') AS C1
LEFT JOIN (SELECT * FROM SC WHERE Cid = '02') AS C2
ON C1.sid = C2.sid
INNER JOIN Student AS C3
ON C1.sid = C3.sid;
sid | sname | sage | ssex | score1 | score2
------------+------------------------+------------+-------+--------+--------
02 | 钱电 | 1990-12-21 | 男 | 70 | 60
03 | 孙风 | 1990-12-20 | 男 | 80 | 80
04 | 李云 | 1990-12-06 | 男 | 50 | 30
05 | 周梅 | 1991-12-01 | 女 | 76 | 87
06 | 吴兰 | 1992-01-01 | 女 | 31 | NULL
01 | 赵雷 | 1990-01-01 | 男 | | 90
(6 行记录)
看完网络答案才理解题目意思……
题目理解:提取学生信息以及学生‘01’ ‘02’课程成绩,如果学生没有选修‘02’课程则成绩显示为null(大神应该是默认score非空,博主以后也有很多程序默认score为非空……)
知识点: left join、coalesce、cast