10-45 結合対象を求めて外部結合

SQLパズル

Detailテーブル     Headテーブル
ID  値段          ID  名前           数量
--  ----          --  -------------  ----
 1   100           1  製品A             3
 1   200           1  製品A(xx限定)     1
 1   300           1  製品A(yy限定)     2
 1   400           2  製品B             2
 1   500           2  製品B(zz限定)     1
 2   100           3  製品C             2
 2   200           4  製品D             2
 3   800

DetailテーブルとHeadテーブルを外部結合させて出力する
(同一IDで、Detailテーブルは値段の昇順、Headテーブルは名前の昇順で結合させる)

DetailテーブルとHeadテーブルのエンティティは、1対多とする

出力結果
ID  名前           値段
--  -------------  ---
 1  製品A          100
 1  製品A          200
 1  製品A          300
 1  製品A(xx限定)  400
 1  製品A(yy限定)  500
 2  製品B          100
 2  製品B          200
 2  製品B(zz限定)  null
 3  製品C          800
 4  製品D          null

データ作成スクリプト

create table Detail(
ID   number(1),
値段 number(3));

insert into Detail values(1,100);
insert into Detail values(1,200);
insert into Detail values(1,300);
insert into Detail values(1,400);
insert into Detail values(1,500);
insert into Detail values(2,100);
insert into Detail values(2,200);
insert into Detail values(3,800);

create table Head(
ID   number(1),
名前 varchar2(13),
数量 number(1));

insert into Head values(1,'製品A'        ,3);
insert into Head values(1,'製品A(xx限定)',1);
insert into Head values(1,'製品A(yy限定)',2);
insert into Head values(2,'製品B'        ,2);
insert into Head values(2,'製品B(zz限定)',1);
insert into Head values(3,'製品C',2);
insert into Head values(4,'製品D',2);
commit;

SQL

select a.ID,a.名前,b.値段
from (select ID,名前,数量,
      sum(数量) over(partition by ID order by 名前) as 数量累計
      from Head) a,
     (select ID,値段,
      Row_Number() over(partition by ID order by 値段) as Rank
      from Detail) b
where a.ID = b.ID(+)
  and b.Rank(+) between a.数量累計-a.数量+1 and a.数量累計
order by a.ID,a.名前;

解説

sum関数とRow_Number関数で結合対象を求めて、
外部結合させてます

---