【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)