经典!SELECT * FROM SC WHERE SC.S#=S.S# AND SC.C#=C.C# 如果S表中当前学号S.S#学习了C表中当前课程C.C#, 上面的语句会返回一条记录(应该是S表中当前学号S.S#学习了C表中当前课程C.C#的成绩记录).SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND SC.C#=C.C#) 语句可以看成: SELECT * FROM C WHERE NOT EXISTS (S表中当前学号S.S#学习了C表中当前课程C.C#的成绩记录) 也就是说找出C表中S.S#没有学习的课程.表达式: NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND SC.C#=C.C#)) 只有在 "C表中没有S.S#没有学习的课程" 时为 "真".这个语句大概是SQL研究的一个示例.
笨方法: select sname from s where s# in(select s# from sc where s# in(select b from (select count(1) a,s# b from sc group by s#,c#) where a=(select count(1) from c)));估计速度很慢!
此方法只能对数据库设计比较严格的才有用,即表之间有存在性关系 select sname from s where s# in(select s# from sc where s# in(select b from (select count(1) a,s# b from sc group by s#,c#) where a=(select count(distinct(c#) from c)));
另一种方法: SELECT SNAME FROM S WHERE EXISTS (SELECT * --或任意列 FROM C left join (select C from SC where SC.S#=S.S#) SC1 on C.C#=SC1.C# WHERE SC1.C# is null )
大概是这样的意思吧:只有两个条件都成立的时候才查询. if NOT EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND SC.C#=C.C#)) begin if NOT EXISTS (SELECT * FROM C ) SELECT SNAME FROM S end else SELECT SNAME FROM S WHERE 1>2
如果S表中当前学号S.S#学习了C表中当前课程C.C#, 上面的语句会返回一条记录(应该是S表中当前学号S.S#学习了C表中当前课程C.C#的成绩记录).SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND SC.C#=C.C#)
语句可以看成:
SELECT * FROM C WHERE NOT EXISTS (S表中当前学号S.S#学习了C表中当前课程C.C#的成绩记录)
也就是说找出C表中S.S#没有学习的课程.表达式:
NOT EXISTS (SELECT * FROM C WHERE NOT EXISTS (SELECT * FROM SC WHERE SC.S#=S.S# AND SC.C#=C.C#))
只有在 "C表中没有S.S#没有学习的课程" 时为 "真".这个语句大概是SQL研究的一个示例.
select sname
from s
where s# in(select s#
from sc
where s# in(select b
from (select count(1) a,s# b
from sc group by s#,c#)
where a=(select count(1) from c)));估计速度很慢!
select sname
from s
where s# in(select s#
from sc
where s# in(select b
from (select count(1) a,s# b
from sc group by s#,c#)
where a=(select count(distinct(c#) from c)));
SELECT SNAME
FROM S
WHERE EXISTS
(SELECT * --或任意列
FROM C left join (select C from SC where SC.S#=S.S#) SC1
on C.C#=SC1.C#
WHERE SC1.C# is null
)
if NOT EXISTS
(SELECT *
FROM SC
WHERE SC.S#=S.S#
AND SC.C#=C.C#))
begin
if NOT EXISTS
(SELECT *
FROM C )
SELECT SNAME
FROM S
end
else
SELECT SNAME
FROM S
WHERE 1>2