从“飞行员与飞机”问题详解Exists用法

飞行员与飞机问题在我另一篇博文中：http://blog.csdn.net/iijse/archive/2010/11/12/6004332.aspx

 

Exists和Not Exists 用法，很多人不太懂，特别涉及到嵌套查询，比如以下几问题：

 

12）查询至少使用了J1工程所用的几类零件的工程的工程号。

13）查询至少使用了所有红色零件的工程号。 

17）查询至少使用了供应商S1供应的全部零件的工程号JNO。 

18）查询使用了全部上海产的零件的工程号JNO。 

19）查询没有使用J2所使用的所有零件的工程号JNO。

 

SQL语言中没有全称量词，所以没有办法直接比较“一些的一些值”都是“另一些的一些值”，诸如此类的问题，所以需要我们转化一下问题：

不存在一些的一些值不在另一些的一些值范围之内

有点拗，下面拿飞行员与飞机问题来详细分析：

select distinct 飞行员姓名 from 飞行技巧表 a where Not Exists ( select 飞机型号 from 飞机棚 f1 where Not Exists ( select * from 飞行技巧表 b where f1.飞机型号=b.飞机型号 and a.飞行员姓名=b.飞行员姓名 ) )  

1. 首先最外一层遍历“飞行技巧表”中的每一条记录，条件是：
2. 遍历飞机棚中的每一架飞机，条件是：对于某位飞行员（与最外层查询连接）和飞机棚中的所有飞机，不存在他不会开的飞机。
3. 如果有一架他不会开，那第二层嵌套查询的条件（Not Exists）就为TRUE了（因为最内层查询至少有一条查询结果），那么这时第二层查询所遍历的这条记录也就不符合条件，不被列入到第二层查询的结果集中。
4. 也就是说：第二层和第三层查询的是针对于某位飞行员，列出飞机棚中的飞机他不会开的有哪几架。
5. 如果他全都会开，那么最外层循环的条件（Not Exists）便会满足，于是这名飞行员就会被列入到最终的结果集中。

弄清楚这个问题之后，这类的问题应该都能够理解了。或者也可以试着像老师那样画三个表分析。

 

总结一句：不存在一个元素不在那范围之内，那么就是所有元素都在这范围里.

 

PS:

当然这种问题不止这一种解决方法（使用Exists嵌套查询），还可以通过数数的方式，比如这道飞行员与飞机的问题，另一种解决方法是：

 

 

 

select distinct 飞行员姓名 from 飞行技巧表 f1 inner join 飞机棚 f2 on f1.飞机型号=f2.飞机型号 group by 飞行员姓名 having count(f1.飞机型号)=( select count(*) from 飞机棚 ); /*或者是*/ select distinct 飞行员姓名 from 飞行技巧表 f1,飞机棚 f2 where f2.飞机型号=f1.飞机型号 group by 飞行员姓名 having COUNT(f1.飞机型号)=(select COUNT(*) from 飞机棚);

通过比对执行查询的开销，发现这一种方法更节省。详细的测试结果可以看这篇博文：http://blog.csdn.net/iijse/archive/2010/11/12/6004332.aspx