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     number(2),
description char(7));

insert into Items values(10,'Item10');
insert into Items values(20,'Item20');
insert into Items values(30,'Item30');
insert into Items values(40,'Item40');
insert into Items values(50,'Item50');

create table Actuals(
Item_no  number(2),
act      number(3),
check_no char(4));

insert into Actuals values(10,300,'1111');
insert into Actuals values(20,325,'2222');
insert into Actuals values(20,100,'3333');
insert into Actuals values(30,525,'1111');

create table Estimates(
Item_no number(2),
est     number(3));

insert into Estimates values(10,300);
insert into Estimates values(10, 50);
insert into Estimates values(20,325);
insert into Estimates values(20,110);
insert into Estimates values(40, 25);
commit;

SQL

--■■■インラインビューと外部結合を使う方法■■■
select a.Item_no,a.description,b.tot_act,c.tot_est,b.check_no
  from Items a
  Left Join (select Item_no,sum(act) as tot_act,
             case when count(check_no) >=2 then 'Mixed'
                  when count(check_no) = 1 then max(check_no) end as check_no
               from Actuals
             group by Item_no) b
    on b.Item_no = a.Item_no
  Left Join (select Item_no,sum(est) as tot_est
               from Estimates
             group by Item_no) c
    on c.Item_no = a.Item_no
where nvl(b.Item_no,c.Item_no) is not null
order by a.Item_no;

--■■■unionとサブクエリを使う方法■■■
select a.Item_no,
(select b.description from Items b where b.Item_no=a.Item_no) as description,
(select sum(b.act) from Actuals b where b.Item_no=a.Item_no) as tot_act,
(select sum(b.est) from Estimates b where b.Item_no=a.Item_no) as tot_est,
case when (select count(b.Item_no) from Actuals b
            where b.Item_no=a.Item_no
              and RowNum <= 2 ) = 2 then 'Mixed'
else (select b.check_no from Actuals b where b.Item_no=a.Item_no) end as check_no
from (select Item_no from Actuals
      union select Item_no from Estimates) a
order by a.Item_no;

--■■■完全外部結合と分析関数を使う方法■■■
with ActualsView as(
select distinct Item_no,
sum(act) over(partition by Item_no) as tot_act,
case when count(act) over(partition by Item_no) >= 2
     then 'Mixed' else check_no end as check_no
from Actuals),
EstimatesView as(
select distinct Item_no,
sum(est) over(partition by Item_no) as tot_est
  from Estimates)
select coalesce(a.Item_no,b.Item_no) as Item_no,
c.description,a.tot_act,b.tot_est,a.check_no
  from ActualsView a full join EstimatesView b
    on a.Item_no = b.Item_no
  Left Join Items c
    on coalesce(a.Item_no,b.Item_no) = c.Item_no
order by Item_no;

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

解説

unionで和集合を求めて、Item_noの一覧を作成し、
select句でスカラー問い合わせを使う方法や、

完全外部結合と分析関数を使う方法などがあります。

---