嵌套NOT EXISTS
有个学生学号\选修课程对应表SC(SNO,CNO),现在查询至少选修了学生95002选修的课程的学生学号,实现的SQL是这样的:
SELECT   *   FROM   SC   A  
WHERE   NOT   EXISTS(   SELECT   *   FROM   SC   B   WHERE   B.SNO=95002   AND  
NOT   EXISTS  
(SELECT   *   FROM   SC   C   WHERE   C.CNO=B.CNO   AND   C.SNO=A.SNO))
谁能帮忙读懂!!!

------解决方案--------------------
create table sc(SNO varchar(20), CNO varchar(20), 成绩 int)
insert sc select '001 ', 'english ', 80
union all select '001 ', 'chinese ', 70
union all select '002 ', 'english ', 75
union all select '002 ', 'chinese ', 90
union all select '002 ', 'english1 ', 85
union all select '003 ', 'chinese ', 80
union all select '004 ', 'english ', 80
union all select '004 ', 'china ', 80

也可以为in,楼主要的结果用判断直假的方式效率好:

select * from sc where sno in(
select SNO from sc a where cno in(select cno from sc where SNO= '001 ')
group by SNO
having count(1)=(select count(1) from sc where SNO= '001 '))

--举例说明楼主的语句
SELECT * FROM sc A
WHERE NOT EXISTS
(
SELECT * FROM sc B WHERE B.SNO= '001 '
AND
NOT EXISTS
(SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO=A.SNO)
)

not exists--条件为假时成立
扫描第一条记录时条件为:

SELECT * FROM sc B WHERE B.SNO= '001 '
AND
NOT EXISTS
(SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO= '001 ')--为假成立,所有C.SNO=001的都成立
当C.SNO= '002 ';

SELECT * FROM sc B WHERE B.SNO= '001 '
AND
NOT EXISTS
(SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO= '002 ')--为假成立,所有C.SNO=002的都成立
当C.SNO= '003 ';

SELECT * FROM sc B WHERE B.SNO= '001 '
AND
NOT EXISTS
(SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO= '003 ')--有一条记录显示为真不成立
/*

SNO CNO 成绩
-------------------- -------------------- -----------
001 english 80

（所影响的行数为 1 行）*/

当C.SNO= '004 ';
SELECT * FROM sc B WHERE B.SNO= '001 '
AND
NOT EXISTS
(SELECT * FROM sc C WHERE C.CNO=B.CNO AND C.SNO= '004 ')--有一条记录显示为真不成立
/*
SNO CNO 成绩
-------------------- -------------------- -----------
001 chinese 70

（所影响的行数为 1 行）*/