sql-server-2005 - SQL 搜索另一个表中的行

Question

给出了两张表

试点组表

Pilot            Plane  

Jon Smith      A1-Fighter
Jon Smith   B1-Fighter
Gravell     A2-Fighter
Jon Skeet   A1-Fighter
Jon Skeet   B1-Fighter
Jon Skeet   A4-Fighter
Gravell     A5-Fighter

南方操作表

Plane
A1-Fighter
B1-Fighter

任务是打印接受过 A1-Fighter 和 B1-Fighter 训练的飞行员姓名。

我通过执行得到了结果

select distinct pilot from PilotGroup  as pg1
   where not exists
        ( 
           select * from SouthOperation Sop where 
              not exists
              ( 
                select *from PilotGroup  as pg2 where ( pg1.pilot = pg2.pilot) 
                 and 
                (pg2.plane= Sop.plane)
               )
        )

是否有其他方法可以根据其他表搜索行？

Answer 1

select 
    pilot 
from 
    pilotgroup pg 
    inner join
    southoperation sop on (pg.plane = sop.plane)
group by 
    pilot
having 
    count(pg.plane) = (select count(*) from southoperation)