9-28 等しい集合が存在する、集合を求める

SQLパズル

PartsTable1    PartsTable2
ID   Val       ID   Val
---  ---       ---  ---
AAA   10       AAA   10
AAA   20       AAA   30
BBB   10       BBB   10
BBB   40       BBB   40
CCC   40       CCC   30
CCC   50       DDD   60
DDD   60       DDD   70
EEE   80       EEE   80
FFF   10       FFF   10
FFF   70       FFF   30
GGG   20

PartsTable1とPartsTable2の、IDごとの集合で
互いに等しい集合を出力する

PartsTable1とPartsTable2に、
重複行は存在しないものとする

出力結果
ID   Val
---  ---
BBB   10
BBB   40
EEE   80

SQLパズル(日本語版)のパズル26 [等集合の発見] を参考にさせていただきました
SQLパズル 第2版のパズル27 [等しい集合を見つける] を参考にさせていただきました

データ作成スクリプト

create table PartsTable1 as
select 'AAA' as ID,10 as Val from dual
union select 'AAA',20 from dual
union select 'BBB',10 from dual
union select 'BBB',40 from dual
union select 'CCC',40 from dual
union select 'CCC',50 from dual
union select 'DDD',60 from dual
union select 'EEE',80 from dual
union select 'FFF',10 from dual
union select 'FFF',70 from dual
union select 'GGG',20 from dual;

create table PartsTable2 as
select 'AAA' as ID,10 as Val from dual
union select 'AAA',30 from dual
union select 'BBB',10 from dual
union select 'BBB',40 from dual
union select 'CCC',30 from dual
union select 'DDD',60 from dual
union select 'DDD',70 from dual
union select 'EEE',80 from dual
union select 'FFF',10 from dual
union select 'FFF',30 from dual;

SQL

--■■■分析関数を使う方法1■■■
select ID,Val
from (select a.ID,a.Val,a.結合可能数,count(*) over(partition by a.ID) as 結合数
        from (select ID,Val,count(*) over(partition by ID) as 結合可能数
                from PartsTable1) a,
             (select ID,Val,count(*) over(partition by ID) as 結合可能数
                from PartsTable2) b
       where a.ID  = b.ID
         and a.Val = b.Val
         and a.結合可能数 = b.結合可能数)
 where 結合可能数 = 結合数;

--■■■分析関数を使う方法2■■■
select ID,Val
  from PartsTable1 a
 where not exists(select b.ID,b.Val,count(*) over()
                    from PartsTable1 b
                   where b.ID = a.ID
                   minus
                  select b.ID,b.Val,count(*) over()
                    from PartsTable2 b
                   where b.ID = a.ID);

--■■■分析関数を使わない方法1■■■
select ID,Val
  from PartsTable1 a
 where not exists(select b.ID,b.Val
                    from PartsTable1 b
                   where b.ID = a.ID
                   minus
                  select b.ID,b.Val
                    from PartsTable2 b
                   where b.ID = a.ID)
   and not exists(select b.ID,b.Val
                    from PartsTable2 b
                   where b.ID = a.ID
                   minus
                  select b.ID,b.Val
                    from PartsTable1 b
                   where b.ID = a.ID);

--■■■分析関数を使わない方法2■■■
select ID,Val
  from PartsTable1 a
 where not exists(select b.ID,b.Val
                    from PartsTable1 b
                   where b.ID = a.ID
                   minus
                  select b.ID,b.Val
                    from PartsTable2 b
                   where b.ID = a.ID)
   and (select count(*) from PartsTable1 b
         where b.ID = a.ID) =
       (select count(*) from PartsTable2 b
         where b.ID = a.ID);

解説

IDの等しい集合を求めて、集合演算を行ってます

---