create table 工場(飛行機名 text);
create table 操縦士(
操縦士ID smallint,
操縦可能機 text);
--テストパターン1 正常パターン
delete from 工場;
delete from 操縦士;
insert into 工場 values
('ジャビィ'),
('ハンター'),
('ファルコ');
insert into 操縦士 values
(1,'ジャビィ'),
(2,'ファルコ'),
(3,'ハンター'),
(4,'ジャビィ'),
(4,'ファルコ'),
(5,'ジャビィ'),
(5,'ハンター'),
(5,'ファルコ'),
(5,'ペリカン'),
(6,'ハンター'),
(6,'ペリカン'),
(7,'ジャビィ'),
(7,'ファルコ'),
(7,'ペリカン'),
(8,'ジャビィ'),
(8,'ハンター'),
(8,'ファルコ');
--テストパターン2 特殊パターン
delete from 工場;
delete from 操縦士;
insert into 工場 values('ラップル');
insert into 操縦士 values
(1,'ジャビィ'),
(2,'ファルコ'),
(3,'ハンター'),
(4,'ジャビィ'),
(4,'ファルコ'),
(5,'ジャビィ'),
(5,'ハンター'),
(5,'ファルコ'),
(5,'ペリカン'),
(6,'ハンター'),
(6,'ペリカン'),
(7,'ジャビィ'),
(7,'ファルコ'),
(7,'ペリカン'),
(8,'ジャビィ'),
(8,'ハンター'),
(8,'ファルコ');
--テストパターン3 空集合
delete from 工場;
delete from 操縦士;
insert into 操縦士 values
(1,'ジャビィ'),
(2,'ファルコ'),
(3,'ハンター'),
(4,'ジャビィ'),
(4,'ファルコ'),
(5,'ジャビィ'),
(5,'ハンター'),
(5,'ファルコ'),
(5,'ペリカン'),
(6,'ハンター'),
(6,'ペリカン'),
(7,'ジャビィ'),
(7,'ファルコ'),
(7,'ペリカン'),
(8,'ジャビィ'),
(8,'ハンター'),
(8,'ファルコ');
--■■■配列型を使う方法1■■■
select 操縦士ID,操縦可能機
from (select 操縦士ID,操縦可能機,
array_agg(操縦可能機) over(partition by 操縦士ID) as planeAgg
from 操縦士) a
where not exists(select 1 from 工場 b
where b.飛行機名 != all(a.planeAgg))
order by 操縦士ID,操縦可能機;
--■■■配列型を使う方法2(上記をドモルガンの法則で、存在否定命題から全称肯定命題に変形)■■■
select 操縦士ID,操縦可能機
from (select 操縦士ID,操縦可能機,
array_agg(操縦可能機) over(partition by 操縦士ID) as planeAgg
from 操縦士) a
where true = all(select b.飛行機名 = any(a.planeAgg)
from 工場 b);
--■■■配列型を使う方法3(上記の全称肯定命題を、bool_and関数で実装)■■■
select 操縦士ID,操縦可能機
from (select 操縦士ID,操縦可能機,
array_agg(操縦可能機) over(partition by 操縦士ID) as planeAgg
from 操縦士) a
where (select bool_and(b.飛行機名 = any(a.planeAgg))
from 工場 b) is not false;
--■■■配列型を使う方法4(where句のサブクエリは必ず行を返すので、上記のSQLを微修正)■■■
select 操縦士ID,操縦可能機
from (select 操縦士ID,操縦可能機,
array_agg(操縦可能機) over(partition by 操縦士ID) as planeAgg
from 操縦士) a
where (select bool_and(b.飛行機名 = any(a.planeAgg)) is not false
from 工場 b);
--■■■配列型を使う方法5■■■
select 操縦士ID,操縦可能機
from (select 操縦士ID,操縦可能機,
array_agg(操縦可能機) over(partition by 操縦士ID) as planeAgg
from 操縦士) a
where (select (array_agg(b.飛行機名) <@ a.planeAgg) is not false
from 工場 b);
--■■■配列型を使う方法6■■■
select 操縦士ID,操縦可能機
from (select 操縦士ID,操縦可能機,
array_agg(操縦可能機) over(partition by 操縦士ID) as planeAgg
from 操縦士) a
where exists (select 1
from 工場 b
having count(*) = 0
or array_agg(b.飛行機名) <@ a.planeAgg);
--■■■except集合演算を使う方法■■■
select 操縦士ID,操縦可能機
from 操縦士 a
where not exists(select b.飛行機名
from 工場 b
except all
select c.操縦可能機 from 操縦士 c
where c.操縦士ID = a.操縦士ID)
order by 操縦士ID,操縦可能機;