9-68 結合が先か、Sum関数が先か その1

SQLパズル

mainT                 sumT
Key1  Key2  name      Key1  Key2  Code  Val
----  ----  ----      ----  ----  ----  ---
   1     1  AAAA         1     1  A     10
   1     3  BBBB         1     1  B     20
   1     4  CCCC         1     1  C     40
   2     2  DDDD         1     2  A     80
   2     4  EEEE         1     3  A     70
                         1     3  B     10
                         2     4  B     30
                         2     4  C     50

mainTにKey1とKey2をキーとして、sumTと外部結合させ、
CodeがAのValの合計
CodeがBのValの合計
CodeがCのValの合計
を求める。

出力結果
Key1  Key2  name  ASum  BSum  CSum
----  ----  ----  ----  ----  ----
   1     1  AAAA    10    20    40
   1     3  BBBB    70    10  null
   1     4  CCCC  null  null  null
   2     2  DDDD  null  null  null
   2     4  EEEE  null    30    50

達人に学ぶ SQL徹底指南書の92ページを参考にさせていただきました

データ作成スクリプト

create table mainT(Key1,Key2,name) as
select 1,1,'AAAA' from dual union all
select 1,3,'BBBB' from dual union all
select 1,4,'CCCC' from dual union all
select 2,2,'DDDD' from dual union all
select 2,4,'EEEE' from dual;

create table sumT(Key1,Key2,Code,Val) as
select 1,1,'A',10 from dual union all
select 1,1,'B',20 from dual union all
select 1,1,'C',40 from dual union all
select 1,2,'A',80 from dual union all
select 1,3,'A',70 from dual union all
select 1,3,'B',10 from dual union all
select 2,4,'B',30 from dual union all
select 2,4,'C',50 from dual;

SQL

---■■■結合を先に行う方法■■■
select a.Key1,a.Key2,a.name,
sum(decode(b.Code,'A',b.Val)) as ASum,
sum(decode(b.Code,'B',b.Val)) as BSum,
sum(decode(b.Code,'C',b.Val)) as CSum
  from mainT a Left Join sumT b
    on (a.Key1 = b.Key1
    and a.Key2 = b.Key2)
group by a.Key1,a.Key2,a.name
order by a.Key1,a.Key2;

---■■■結合を後に行う方法■■■
select a.Key1,a.Key2,a.name,b.ASum,b.BSum,b.CSum
  from mainT a
  Left Join
      (select Key1,Key2,
              sum(decode(Code,'A',Val)) as ASum,
              sum(decode(Code,'B',Val)) as BSum,
              sum(decode(Code,'C',Val)) as CSum
         from sumT
      group by Key1,Key2) b
    on (b.Key1 = a.Key1 and b.Key2 = a.Key2)
order by a.Key1,a.Key2;

---■■■スカラーサブクエリを何度も使う方法■■■
select Key1,Key2,name,
(select sum(decode(b.Code,'A',Val)) from sumT b where b.Key1 = a.Key1 and b.Key2 = a.Key2) as ASum,
(select sum(decode(b.Code,'B',Val)) from sumT b where b.Key1 = a.Key1 and b.Key2 = a.Key2) as BSum,
(select sum(decode(b.Code,'C',Val)) from sumT b where b.Key1 = a.Key1 and b.Key2 = a.Key2) as CSum
  from mainT a
order by Key1,Key2;

解説

先に結合できるかどうかを判断し、
可能なら先に結合させるといいでしょう。

---