9-67 Toddの除算

SQLパズル

JobParts      SubParts
job  SNo      pnoJob  SNo
---  ---      ------  ---
j1   p1       s1      p1
j1   p2       s1      p2
j2   p2       s1      p3
j2   p4       s1      p4
j2   p5       s1      p5
j3   p2       s1      p6
              s2      p1
              s2      p2
              s3      p2
              s4      p2
              s4      p4
              s4      p5

JobPartsテーブルのjobごとで、
全てのSNoを持つpnoJobの組み合わせを求める。

出力結果
job  pnoJob
---  ------
j1   s1
j1   s2
j2   s1
j2   s4
j3   s1
j3   s2
j3   s3
j3   s4

プログラマのためのSQL第2版の19章[データの分割]を参考にさせていただきました

データ作成スクリプト

create table JobParts(job,SNo) as
select 'j1','p1' from dual union all
select 'j1','p2' from dual union all
select 'j2','p2' from dual union all
select 'j2','p4' from dual union all
select 'j2','p5' from dual union all
select 'j3','p2' from dual;

create table SubParts(pnoJob,SNo) as
select 's1','p1' from dual union all
select 's1','p2' from dual union all
select 's1','p3' from dual union all
select 's1','p4' from dual union all
select 's1','p5' from dual union all
select 's1','p6' from dual union all
select 's2','p1' from dual union all
select 's2','p2' from dual union all
select 's3','p2' from dual union all
select 's4','p2' from dual union all
select 's4','p4' from dual union all
select 's4','p5' from dual;

SQL

--■■■partitioned Outer Joinを使う方法(10g以降)■■■
select a.job,b.pnoJob
  from JobParts a
  Left Join SubParts b
  partition by (b.pnoJob)
    on a.SNo = b.SNo
group by a.job,b.pnoJob
having count(*) = count(b.SNo)
order by a.job,b.pnoJob;

--■■■分析関数と内部結合を使う方法■■■
select a.job,b.pnoJob
from (select job,SNo,count(*) over(partition by job) as NeedCnt
        from JobParts) a,SubParts b
 where a.SNo = b.SNo
group by a.job,a.NeedCnt,b.pnoJob
having count(*) = NeedCnt
order by a.job,b.pnoJob;

解説

この場合は、空集合を考慮しなくていいので、
内部結合が使えますね。

9-9 差集合が空集合かチェック

---