9-63 導出テーブルを減らせ

SQLパズル

Accountsテーブル
acct_nbr
--------
       1
       2
       3
       4
       8

Fooテーブル            Barテーブル
acct_nbr  foo_qty      acct_nbr  bar_qty
--------  -------      --------  -------
       1       10             2      160
       2       20             3      320
       2       40             3      640
       3       80             3        1
       8      111             8      111
       8      222             8      222
       8      333             8      333

Accountsテーブルのacct_nbrに紐づく、foo_qtyの合計と
Accountsテーブルのacct_nbrに紐づく、bar_qtyの合計
を求める。

出力結果
acct_nbr  foo_qty_tot  bar_qty_tot
--------  -----------  -----------
       1           10            0
       2           60          160
       3           80          961
       4            0            0
       8          666          666

SQLパズル 第2版のパズル74 [導出テーブルを減らせ] を参考にさせていただきました

データ作成スクリプト

create table Accounts(acct_nbr number(1) primary key);

insert into Accounts values(1);
insert into Accounts values(2);
insert into Accounts values(3);
insert into Accounts values(4);
insert into Accounts values(8);

create table Foo(
acct_nbr number(1) references Accounts(acct_nbr),
foo_qty  number(3));

insert into Foo values(1, 10);
insert into Foo values(2, 20);
insert into Foo values(2, 40);
insert into Foo values(3, 80);
insert into Foo values(8,111);
insert into Foo values(8,222);
insert into Foo values(8,333);

create table Bar(
acct_nbr number(1) references Accounts(acct_nbr),
bar_qty  number(3));

insert into Bar values(2,160);
insert into Bar values(3,320);
insert into Bar values(3,640);
insert into Bar values(3,  1);
insert into Bar values(8,111);
insert into Bar values(8,222);
insert into Bar values(8,333);
commit;

SQL

--■■■インラインビューで外部結合する方法■■■
select aa.acct_nbr,foo_qty_tot,sum(nvl(c.bar_qty,0)) as bar_qty_tot
from (select a.acct_nbr,sum(nvl(b.foo_qty,0)) as foo_qty_tot
        from Accounts a Left Join Foo b
          on a.acct_nbr = b.acct_nbr
       group by a.acct_nbr) aa
Left Join Bar c
  on aa.acct_nbr = c.acct_nbr
group by aa.acct_nbr,foo_qty_tot
order by aa.acct_nbr;

--■■■union allを使う方法■■■
select a.acct_nbr,
sum(nvl(b.foo_qty,0)) as foo_qty_tot,
sum(nvl(b.bar_qty,0)) as bar_qty_tot
  from Accounts a
Left Join (select acct_nbr,foo_qty,0 as bar_qty from Foo
           union all
           select acct_nbr,0 as foo_qty,bar_qty from Bar) b
  on a.acct_nbr = b.acct_nbr
group by a.acct_nbr
order by a.acct_nbr;

解説

union allを使う方法が分かりやすいと思います。

---