10-339 等しい集合を持つグループを、外部結合で見つける

SQLパズル

ordテーブル           mapテーブル
ordID  prodID         pkgID  prodID
-----  ------         -----  ------
  101       1            10       1
  101       2            10       2
  102       1            20       1
  102       3            20       3
  103       1            30       2
  103       2            30       3
  103       3            40       1
  104       1            40       2
                         40       3

ordテーブルのordIDごとに
mapテーブルのpkgIDごとで、prodIDと数と値が一致する、pkgIDを表示する。
該当pkgIDがなければnullとする。

出力結果
ordID  pkgID
-----  -----
  101      1
  102      2
  103      4
  104   null

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table ord(ordID,prodID) as
select 101,1 from dual union all
select 101,2 from dual union all
select 102,1 from dual union all
select 102,3 from dual union all
select 103,1 from dual union all
select 103,2 from dual union all
select 103,3 from dual union all
select 104,1 from dual;

create table map(pkgID,prodID) as
select 10,1 from dual union all
select 10,2 from dual union all
select 20,1 from dual union all
select 20,3 from dual union all
select 30,2 from dual union all
select 30,3 from dual union all
select 40,1 from dual union all
select 40,2 from dual union all
select 40,3 from dual;

SQL

--■■■分析関数のcount関数を使う方法■■■
select ordID,max(pkgID) as pkgID
from (select a.ordID,
      decode(a.cnt,count(*),b.pkgID) as pkgID
      from (select ordID,prodID,
            count(*) over(partition by ordID) as cnt
            from ord) a
           Left Join
           (select pkgID,prodID,
            count(*) over(partition by pkgID) as cnt
            from map) b
        on a.cnt=b.cnt
       and a.prodID=b.prodID
      group by a.ordID,a.cnt,b.pkgID)
group by ordID
order by ordID;

--■■■ListAgg関数を使う方法(11gR2以降)■■■
select a.ordID,b.pkgID
from (select ordID,
      ListAgg(to_char(prodID),',') within group (order by prodID) as ListProdID
      from ord
      group by ordID) a,
     (select pkgID,
      ListAgg(to_char(prodID),',') within group (order by prodID) as ListProdID
      from map
      group by pkgID) b
where a.ListProdID=b.ListProdID(+)
order by a.ordID;

解説

(集合Ａに重複した要素が存在しない、かつ、集合Ｂに重複した要素が存在しない)
かつ
(Ａ⊂Ｂ、または、Ｂ⊃Ａが成立する)
かつ
集合Ａと集合Ｂの要素数が等しい

かを調べてます。

9-27 集合の包含関係を調べる

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
ListAgg関数を使うのもいいと思います。