Postgresql 第4题

最新推荐文章于 2023-07-18 16:14:27 发布

王不留行

最新推荐文章于 2023-07-18 16:14:27 发布

阅读量108

点赞数

分类专栏：数据库文章标签： sql

本文链接：https://blog.csdn.net/weixin_45448234/article/details/107609145

版权

数据库专栏收录该内容

33 篇文章 0 订阅

订阅专栏

4、查询不存在” 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' not in (select cid  from SC  where SC.sid=S.sid)
AND '02'  in  (select cid  from SC  where SC.sid=S.sid);

    sid     |         sname          |    cid     | score
------------+------------------------+------------+-------
 07         | 郑竹                   | 02         |    89
 07         | 郑竹                   | 03         |    98
(2 行记录)

解法2：

select S.sid ,S.sname, SC1.cid, SC1.score
from  student AS S  
inner join (
select * from SC  where cid='02'
)AS SC1
ON  S.sid=SC1.sid
where  S.sid not in (select sid 
from SC
WHERE cid='01');

   sid     |         sname          |    cid     | score
------------+------------------------+------------+-------
 07         | 郑竹                   | 02         |    89
(1 行记录)

解法3：网络答案

SELECT C2.sid, C3.sname, C3.sage, C3.ssex, 
	C1.score AS score1, C2.score AS score2 
FROM (SELECT * FROM SC WHERE Cid = '01') AS C1 
	RIGHT JOIN (SELECT * FROM SC WHERE Cid = '02') AS C2 
ON C1.sid = C2.sid 
      INNER JOIN Student AS C3 
      ON C2.sid = C3.sid 
WHERE C1.score IS NULL;

没搞懂为什么要选取C1.score，这个值不应该是null吗？
实践解决上述问题（虽然为null但是列名还是显示的！）

    sid     |         sname          |    sage    | ssex  | score1 | score2
------------+------------------------+------------+-------+--------+--------
 01         | 赵雷                   | 1990-01-01 | 男    |        |     90
 07         | 郑竹                   | 1989-01-01 | 女    |        |     89
(2 行记录)