当前位置: 代码迷 >> Oracle开发 >> not exists,该如何解决
  详细解决方案

not exists,该如何解决

热度:468   发布时间:2016-04-24 07:35:23.0
not exists
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表选修全部课程
  相关解决方案