【SQL解惑】谜题21：飞机与飞行员

一、创建表与插入数据

create  table  PilotSkills

( pilot  char ( 15 )  not  null,

plane  char ( 15 )  not  null,

primary  key  ( pilot ,  plane ))

insert  into  PilotSkills

values  ( 'Celko' ,  'Piper Cub' ),

          ( 'Higgins' ,  'B-52 Bomber' ),

          ( 'Higgins' ,  'F-14 Fighter' ),

          ( 'Higgins' ,  'Piper Cub' ),

          ( 'Jones' ,  'B-52 Bomber' ),

          ( 'Jones' ,  'F-14 Fighter' ),

          ( 'Smith' ,  'B-1 Bomber' ),

          ( 'Smith' ,  'B-52 Bomber' ),

          ( 'Smith' ,  'F-14 Fighter' ),

          ( 'Wilson' ,  'B-1 Bomber' ),

          ( 'Wilson' ,  'B-52 Bomber' ),

          ( 'Wilson' ,  'F-14 Fighter' ),

          ( 'Wilson' ,  'F-17 Fighter' )

        

create  table  Hangar

( plane  char ( 15 )  primary  key )

insert  into  Hangar

values  ( 'B-1 Bomber' ),

          ( 'B-52 Bomber' ),

          ( 'F-14 Fighter' )

二、查询数据

1、解惑一

select  pilot

   from  PilotSkills  as  p1

  where  not  exists  ( select  *

                      from  Hangar

                     where  not  exists  ( select  *

                                         from  PilotSkills  as  p2

                                        where  ( p1 . pilot  =  p2 . pilot )

                                          and  ( p2 . plane  =  Hangar . plane )))

（1）from PilotSkills as p1语句

选择表PilotSkills的数据，语句中主要选择匹配的是pilot

（2）from Hangar语句

选择表Hangar的数据，语句中主要起作用的是plane

（3）from PilotSkills as p2语句

选择表PilotSkills的数据，语句主要是选择数据与前面的p1和hangar的飞行员和飞机进行匹配

（4）where (p1.pilot = p2.pilot) and (p2.plane = Hangar.plane)语句

将表PilotSkills的数据与前面的p1和hangar的飞行员和飞机进行匹配，按飞行员+现有Hangar表中的飞机进行分配，匹配PilotSkills表中是否存在这样的数据，如果有则select *

（5）select * from Hangar where not exists (... )语句

按飞行员+现有Hangar表中的飞机进行分配，并与PilotSkills表中匹配。如果存在任一数据esists中则返回True，同时又由于not关键字则转换为False，即代表在Hangar表中不选择该飞行员不会驾驶的飞机。如果不存在符合的数据esists中则返回False，同时又由于not关键字则转换为True，即代表在Hangar表中选择到了该飞行员不会驾驶的飞机。

（6）select * from PilotSkills where not exists (... )语句 

如果前面该飞行员在Hangar表中所有飞机都会驾驶，那么exists中则会返回False，又由于关键字not，则变成了True，则代表会选中表PilotSkills中的该名飞行员的这行记录。

如果前面该飞行员在Hangar表中存在某架飞机不会驾驶，那么exists中则会返回True，又由于关键字not，则变成了False，则查询语句的时候，该名飞行员在表PilotSkills中会由于这架不会驾驶的飞机全部都返回False，导致在表PilotSkills中的该名飞行员的所有记录都不会被选到。、

以下是过程：

（1）Higgins与Plane表匹配，然后其中的B-1 Bomber在PilotSkills表中没有找到数据，对于所有的PilotSkills表中的Higgins返回的结果都是一样的。

（2）Wilson与Plane表匹配后，所有的都能找到数据，因此在exists中则不会返回True。

2、解惑二

（1）将PilotSkills表与hangar表的飞机进行匹配，再按pilot飞行员字段分组，只有满足分组内的数量等于hangar表的飞机数则代表该飞行员能否驾驶该飞机表中的所有飞机。

select  pilot

   from  Pilotskills  as  p1 , hangar  as  h1

  where  p1 . plane  =  h1 . plane

  group  by  p1 . pilot

having  COUNT ( p1 . plane )  =  ( select  COUNT (*)  from  Hangar )

3、解惑三

（1）精确匹配

书中的这个语句将leftjoin前的数据与leftjoin后的数据统计的数量来跟Hangar表进行匹配。

select  p1 . pilot

   from  PilotSkills  as  p1

   left  outer  join  Hangar  as  h1

     on  p1 . plane  =  h1 . plane

  group  by  p1 . pilot

having  COUNT ( p1 . plane )  =  ( select  COUNT ( plane )  from  Hangar )

    and  COUNT ( h1 . plane )  =  ( select  COUNT ( plane )  from  Hangar )

（2）模糊匹配

只要飞行员能够驾驶的飞机满足飞机表的飞机即可筛选到。

select  p1 . pilot

   from  PilotSkills  as  p1

   left  outer  join  Hangar  as  h1

     on  p1 . plane  =  h1 . plane

  group  by  p1 . pilot

having  COUNT ( h1 . plane )  =  ( select  COUNT ( plane )  from  Hangar )