SQL> select * from sc;
SNO CNO GRADE
---------- ---------- ----------
1 1 5
1 2 6
1 3 10
2 3 10
3 1 9
SQL> select * from c;
CNO CNAME
---------- ----------
1 Football
2 Basketball
3 Baseball
SQL> select * from s;
SNO SNAME
---------- ----------
1 Messi
2 Owen
3 Rooney
功能：查找选修了全部课程的学生姓名
select sname from s where not exists
(select * from c where not exists
(select * from sc where sc.sno=s.sno and sc.cno=c.cno)
)
这里使用2个not exists怎么解释？看不懂！求解。
------解决方案--------------------
从里向外一层一层的剥开~~~
------解决方案--------------------
select * from sc where sc.sno=s.sno and sc.cno=c.cno
这里取3个表的连接,然后按条件过滤
1 1 5 1 Football 1 Messi
1 2 6 2 Basketball 1 Messi
1 3 10 3 Baseball 1 Messi
2 3 10 3 Baseball 2 Owen
3 1 9 1 Football 3 Rooney
select sname from s where not exists
(select * from c where not exists
(这里是3个表的连接,然后以c表和s表的条件在这里查询,当有记录时为true,没有记录为false)
)
---------------------------
not exists相当于取反,
例如s=1时,
select * from c where not exists
(select * from sc where sc.sno=s.sno and sc.cno=c.cno)
这部分查询结果为空集....
当s=2时
select * from c where not exists
(select * from sc where sc.sno=s.sno and sc.cno=c.cno)
这部分查询结果为
1 Football
2 Basketball
这样表述不知道清楚不....
------解决方案--------------------
可否这样简单的理解：
1.select * from sc where sc.sno=s.sno and sc.cno=c.cno表至少选修了一门课程
2.(select * from c where not exists
(select * from sc where sc.sno=s.sno and sc.cno=c.cno)
)内层not exists表一门都课程都没有选修
3.select sname from s where not exists
(select * from c where not exists
(select * from sc where sc.sno=s.sno and sc.cno=c.cno)
)外层not exists表选修全部课程