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







评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值