10-170 クロスジョインで組み合わせを列挙

SQLパズル

cal_mstテーブル
cal_date
--------
20070101
20070102
20070103
20070104

uri_tblテーブル
shohin_cd  uri_date  kingaku
---------  --------  -------
        A  20070101      100
        A  20070102      200
        B  20070101      111
        B  20070103      333

cal_dateごと、shohin_cdごと
の組み合わせで、以下のデータを出力する。

出力結果
cal_date  shohin_cd  uri_date  kingaku
--------  ---------  --------  -------
20070101          A  20070101      100
20070102          A  20070102      200
20070103          A      null     null
20070104          A      null     null
20070101          B  20070101      111
20070102          B      null     null
20070103          B  20070103      333
20070104          B      null     null

データ作成スクリプト

create table cal_mst(cal_date char(8));

create table uri_tbl(
shohin_cd char(1),
uri_date  char(8),
kingaku   number(3));

insert all
into cal_mst values('20070101')
into cal_mst values('20070102')
into cal_mst values('20070103')
into cal_mst values('20070104')
into uri_tbl values('A','20070101',100)
into uri_tbl values('A','20070102',200)
into uri_tbl values('B','20070101',111)
into uri_tbl values('B','20070103',333)
select 1 from dual;
commit;

SQL

--■■■クロスジョインとグループ化を使う方法■■■
select a.cal_date,b.shohin_cd,
max(decode(a.cal_date,b.uri_date,a.cal_date)) as uri_date,
max(decode(a.cal_date,b.uri_date,b.kingaku))  as kingaku
  from cal_mst a,uri_tbl b
group by a.cal_date,b.shohin_cd
order by b.shohin_cd,a.cal_date;

--■■■SQL99構文で結合させる方法■■■
select c.cal_date,a.shohin_cd,b.uri_date,b.kingaku
  from cal_mst c
  cross join (select distinct shohin_cd from uri_tbl) a
  Left join uri_tbl b on
  (c.cal_date = b.uri_date and a.shohin_cd = b.shohin_cd)
order by a.shohin_cd,c.cal_date;

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select a.cal_date,b.shohin_cd,b.uri_date,b.kingaku
  from cal_mst a
  Left join uri_tbl b
  partition by (b.shohin_cd)
    on (a.cal_date = b.uri_date)
order by b.shohin_cd,a.cal_date;

解説

クロスジョインを使うと、全ての組み合わせを求めることができます。
数学では、直積集合と呼ばれます。

10g以降ならPartitioned Outer Joinを使ってもいいでしょう。
3-34 Partitioned Outer Join

7-75 全ての年月の値を出力

---