9-10 共通集合が空集合かチェック

SQLパズル

工場テーブル    操縦士テーブル
飛行機名        操縦士ID   操縦可能機
--------        --------  ----------
ジャビィ               1    ジャビィ
ハンター               2    ファルコ
ファルコ               3    ハンター
                       4    ジャビィ
                       4    ファルコ
                       5    ジャビィ
                       5    ハンター
                       5    ファルコ
                       5    ペリカン
                       6    ハンター
                       6    ペリカン
                       7    ジャビィ
                       7    ファルコ
                       7    ペリカン
                       8    ジャビィ
                       8    ハンター
                       8    ファルコ

操縦士ID=6の人の操縦可能機の中で、工場にある飛行機(この場合は、ハンター)
を操縦可能な操縦士IDと、操縦可能機を出力する(ただし、操縦士ID=6は出力しない)

出力結果
操縦士ID   飛行機名
--------  ----------
       3    ハンター
       5    ジャビィ
       5    ハンター
       5    ファルコ
       5    ペリカン
       8    ジャビィ
       8    ハンター
       8    ファルコ

データ作成スクリプト

前のSQLパズルと同じ

SQL

--■■■配列型を使わない方法■■■
select 操縦士ID,操縦可能機
  from 操縦士 a
 where 操縦士ID != 6
   and exists(select b.操縦可能機 from 操縦士 b
               where b.操縦士ID = a.操縦士ID
              intersect all
              select c.操縦可能機 from 操縦士 c
               where c.操縦士ID = 6
              intersect all
              select d.飛行機名 from 工場 d)
order by 操縦士ID,操縦可能機;

--■■■any述語を使う方法1■■■
select 操縦士ID,操縦可能機
from (select 操縦士ID,操縦可能機,
      array_agg(操縦可能機) over(partition by 操縦士ID) as arrayPlane
        from 操縦士
       where 操縦士ID != 6) a
 where exists(select 飛行機名 from 工場
              intersect all
              select b.操縦可能機 from 操縦士 b
               where b.操縦士ID = 6
                 and b.操縦可能機 = any(a.arrayPlane))
order by 操縦士ID,操縦可能機;

--■■■any述語を使う方法2■■■
select 操縦士ID,操縦可能機
from (select 操縦士ID,操縦可能機,
      array_agg(操縦可能機) over(partition by 操縦士ID) as arrayPlane
        from 操縦士
       where 操縦士ID != 6) a
 where exists(select 飛行機名 from 工場
               where 飛行機名 = any(a.arrayPlane)
              intersect all
              select b.操縦可能機 from 操縦士 b
               where b.操縦士ID = 6)
order by 操縦士ID,操縦可能機;

--■■■&&述語を使う方法■■■
select 操縦士ID,操縦可能機
from (select 操縦士ID,操縦可能機,
      array_agg(操縦可能機) over(partition by 操縦士ID) as agg1,
      array_agg(case when 操縦士ID = 6
                      and exists(select 1 from 工場 b
                                  where b.飛行機名 = a.操縦可能機)
                     then 操縦可能機 end) over() as agg2
        from 操縦士 a) a
 where 操縦士ID != 6
   and agg1 && agg2
order by 操縦士ID,操縦可能機;

解説

配列型でのany述語で、intersect集合演算を代用してます。
9.17. 配列関数と演算子

OracleSQLパズル 9-10 差集合が空集合かチェック

---