表1:
pilot planemodel
p1 727
p1 737
p2 747
p1 a3000
p1 727表2
planemodel
727
737
a3000
pilot代表飞行员,planemodel代表飞机机型,我现在要用sql实现
查询出会驾驶全部机型的飞行员也就是表3 pilot
p1
怎么实现
pilot planemodel
p1 727
p1 737
p2 747
p1 a3000
p1 727表2
planemodel
727
737
a3000
pilot代表飞行员,planemodel代表飞机机型,我现在要用sql实现
查询出会驾驶全部机型的飞行员也就是表3 pilot
p1
怎么实现
select pilot from (select distinct pilot,planemodel from 表1) 表11 having count(*)=(select count(*) from 表2) group by pilot;
where exists
(select * from 表2
where 表1.planemodel=表2.planemodel);
select t1.pilot
from
(SELECT t1.pilot, count(t1.pilot, t1.planemodel, t2.planemodel) count1
from table1 t1, table2 t2
where t1.planemodel = t2.planemodel
group by t1.pilot, t1.planemodel, t2.planemodel) ta,
(select count(planemodel) count2 from table2) tb
where ta.count1 = tb.count2
where not exists
(select * from 表2
where not exists(
select * from 表1 b
where b.pilot=a.pilot and
b.planemodel=表2.planemodel));
having count(*)>(select count(*)from table2)