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

本文通过具体的SQL语句示例,展示了如何使用子查询、联接和聚合函数等技术,来找出能驾驶所有指定类型飞机的飞行员。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、创建表与插入数据
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、付费专栏及课程。

余额充值