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

SQLパズル

StoreTable       AmountTable
SCode  SName     SCode  yyyymm  Amount
-----  -----     -----  ------  ------
00001  Name1     00001  200704    1000
00002  Name2     00002  200704    2000
00003  Name3     00003  200704    3000
00004  Name4     00004  200704    4000
00005  Name5     00005  200704    5000
                 00001  200705   15000
                 00003  200705   16000
                 00005  200705   17000
                 00002  200706   18000

AmountTableの全てのyyyymmの、
StoreTableのSCodeごとのAmountの値を出力する。

出力結果
SCode  yyyymm  Amount
-----  ------  ------
00001  200704    1000
00002  200704    2000
00003  200704    3000
00004  200704    4000
00005  200704    5000
00001  200705   15000
00002  200705       0
00003  200705   16000
00004  200705       0
00005  200705   17000
00001  200706       0
00002  200706   18000
00003  200706       0
00004  200706       0
00005  200706       0

データ作成スクリプト

create table StoreTable as
select '00001' as SCode,'Name1' as SName from dual
union select '00002','Name2' from dual
union select '00003','Name3' from dual
union select '00004','Name4' from dual
union select '00005','Name5' from dual;

create table AmountTable as
select '00001' as SCode,200704 as yyyymm,1000 as Amount from dual
union select '00002',200704, 2000 from dual
union select '00003',200704, 3000 from dual
union select '00004',200704, 4000 from dual
union select '00005',200704, 5000 from dual
union select '00001',200705,15000 from dual
union select '00003',200705,16000 from dual
union select '00005',200705,17000 from dual
union select '00002',200706,18000 from dual;

SQL

--■■■クロスジョインしてグループ化する方法■■■
select a.SCode,b.yyyymm,
nvl(max(decode(a.SCode,b.SCode,Amount)),0) as Amount
  from StoreTable a,AmountTable b
group by b.yyyymm,a.SCode
order by b.yyyymm,a.SCode;

--■■■SQL99構文で結合させる方法■■■
select a.SCode,b.yyyymm,
nvl(c.Amount,0) as Amount
  from StoreTable a
cross join (select distinct yyyymm from AmountTable) b
 Left join AmountTable c
   on (c.SCode = a.SCode
   and c.yyyymm = b.yyyymm)
order by b.yyyymm,a.SCode;

--■■■相関サブクエリを使う方法■■■
select a.SCode,b.yyyymm,
nvl((select c.Amount
      from AmountTable c
     where c.SCode = a.SCode
       and c.yyyymm = b.yyyymm),0) as Amount
  from StoreTable a
cross join (select distinct yyyymm from AmountTable) b
order by b.yyyymm,a.SCode;

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select a.SCode,b.yyyymm,nvl(b.Amount,0) as Amount
  from StoreTable a
  Left Join AmountTable b
partition by (b.yyyymm)
    on (a.SCode = b.SCode)
order by b.yyyymm,a.SCode;

解説

相関サブクエリを使う方法が
分かりやすいと思いますね。

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

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

---