9-2 和集合を求めて、スカラー問い合わせ

SQLパズル

Itemsテーブル
Item_no  description
-------  -----------
     10  Item10
     20  Item20
     30  Item30
     40  Item40
     50  Item50

Actualsテーブル
Item_no  act  check_no
-------  ---  --------
     10  300  1111
     20  325  2222
     20  100  3333
     30  525  1111

Estimatesテーブル
Item_no  est
-------  ---
     10  300
     10   50
     20  325
     20  110
     40   25

上の3テーブルから以下の出力を行う。

出力結果
Item_no  description  tot_act   tot_est  check_no
-------  -----------  -------   -------  --------
     10  Item10           300       350  1111
     20  Item20           425       435  Mixed
     30  Item30           525      NULL  1111
     40  Item40          NULL        25  NULL

SQLパズル(日本語版)のパズル37 [予算報告書] を参考にさせていただきました
SQLパズル 第2版のパズル41 [予算] を参考にさせていただきました

データ作成スクリプト

create table Items(Item_no,description) as
select 10,'Item10' union
select 20,'Item20' union
select 30,'Item30' union
select 40,'Item40' union
select 50,'Item50';

create table Actuals(Item_no,act,check_no) as
select 10,300,1111 union
select 20,325,2222 union
select 20,100,3333 union
select 30,525,1111;

create table Estimates(Item_no,est) as
select 10,300 union
select 10, 50 union
select 20,325 union
select 20,110 union
select 40, 25;

SQL

--■■■Outer Unionもどきを使う方法■■■
select a.Item_no,a.description,
sum(b.act) as tot_act,
sum(b.est) as tot_est,
case count(b.check_no) when 0 then null
                       when 1 then max(b.check_no)::text
                       else 'Mixed' end as check_no
  from Items a
  Join (select Item_no,act,check_no,null as est from Actuals
        union all
        select Item_no,null,null,est from Estimates) b
    on a.Item_no = b.Item_no
group by a.Item_no,a.description
order by a.Item_no;

解説

Outer Unionもどきの使いどころでしょう。

OracleSQLパズル 9-2 和集合を求めて、スカラー問い合わせ

---