34,590
社区成员
发帖
与我相关
我的任务
分享
select sid from s
where not exists( select 1 from (select sc.sno from c,sc where c.cno=sc.cno) a
where sno=s.sno)
select sid
from S
where not exists(select *
from C
where not exists(select *
from SC
where cno=C.cno AND sno=S.sno))
select sid from S
where not exists(select * from C
where not exists(select * from SC
where cno=C.cno))
--你这种写法查出来只有两种结果,要么一个都查不出来,要么select出来全部。
--如果sc表里有cno跟c表cno一样的,返回true,那么select s表的全部,否则相反
--查询1
SELECT S.sid
FROM S
WHERE NOT EXISTS(
SELECT *
FROM C
WHERE NOT EXISTS(
SELECT *
FROM SC
WHERE SC.cno = C.cno
AND SC.sno = S.sno
)
)
对于S表的(甲 1),判断如下查询2-1是否有结果,若没有结果则返回。
--查询2-1
SELECT *
FROM C
WHERE NOT EXISTS(
SELECT *
FROM SC
WHERE SC.cno = C.cno
AND SC.sno = 1
)
对于C表的11,SC表存在(1,11),不返回;
对于C表的22,SC表不存在(1,22),返回;BREAK
因为22返回,查询2-1有结果,所以查询1的(甲 1)不返回。
对于S表的(丙 3), 判断如下查询2-3是否有结果,若没有结果则返回。
--查询2-3
SELECT *
FROM C
WHERE NOT EXISTS(
SELECT *
FROM SC
WHERE SC.cno = C.cno
AND SC.sno = 3
)
对于C表的11,SC表存在(3,11),不返回;
对于C表的22,SC表存在(3,22),不返回;
对于C表的33,SC表存在(3,33),不返回;
因为查询2-3无结果,所以查询1的(丙 3)返回。
SELECT S.sid
FROM S
WHERE NOT EXISTS(
SELECT *
FROM C
WHERE NOT EXISTS(
SELECT *
FROM SC
WHERE SC.cno = C.cno
AND SC.sno = S.sno
)
)
SELECT S.sid
FROM S
WHERE S.sno IN (
SELECT SC.sno
FROM SC
INNER JOIN C
ON SC.cno = C.cno
GROUP BY SC.sno
HAVING COUNT(*) = (SELECT COUNT(*) FROM C)
)