4-9 階層問い合わせの根を取得

SQLパズル

部品テーブル
親    子  数量
--  ----  ----
 A     B     2
 B     C     3
 C  null     4
 D  null     5
 E     F     6
 F  null     7
 E     G     8
 G  null     9

階層情報と、階層の根と、数量×親の数量(親がない場合は数量)
を出力する

出力結果
根  親    子  数量   数量×親の数量
--  --  ----  ----  --------------
 A   A     B     2               2
 A   B     C     3               6
 A   C  null     4              12
 D   D  null     5               5
 E   E     F     6               6
 E   F  null     7              42
 E   E     G     8               8
 E   G  null     9              72

こちらを参考にさせていただきました

データ作成スクリプト

create table 部品(
親   char(1),
子   char(1),
数量 number(1));

insert into 部品 values('A', 'B',2);
insert into 部品 values('B', 'C',3);
insert into 部品 values('C',null,4);
insert into 部品 values('D',null,5);
insert into 部品 values('E', 'F',6);
insert into 部品 values('F',null,7);
insert into 部品 values('E', 'G',8);
insert into 部品 values('G',null,9);
commit;

SQL

col   根 for a4
col   親 for a4
col   子 for a4
col 数量 for 99

--■■■Connect_by_Rootを使う方法(10g以降)■■■
select Connect_by_Root 親 as 根,
親,子,数量,
nvl(prior 数量,1) * 数量 as "数量×親の数量"
from 部品 a
start with not exists(select 1 from 部品 b
                       where b.子 = a.親)
connect by prior 子 = 親;

--■■■sys_connect_by_pathを使う方法(9i以降)(親が可変長文字列の場合)■■■
select
substr(sys_connect_by_path(親,','),2,
        decode(instr(substr(sys_connect_by_path(親,','),2),','),
               0,Length(substr(sys_connect_by_path(親,','),2)),
               instr(substr(sys_connect_by_path(親,','),2),',')-1)) as 根,
親,子,数量,
nvl(prior 数量,1) * 数量 as "数量×親の数量"
from 部品 a
start with not exists(select 1 from 部品 b
                       where b.子 = a.親)
connect by prior 子 = 親;

--■■■sys_connect_by_pathを使う方法(9i以降)(親が固定長文字列の場合)■■■
select
substr(sys_connect_by_path(親,','),2,1) as 根,
親,子,数量,
nvl(prior 数量,1) * 数量 as "数量×親の数量"
from 部品 a
start with not exists(select 1 from 部品 b
                       where b.子 = a.親)
connect by prior 子 = 親;

--■■■select句で相関サブクエリを使う方法■■■
select
(select distinct Last_Value(親) over(order by Level
Rows between Unbounded Preceding and Unbounded Following)
from 部品 b
start with b.RowID = a.RowID
connect by prior 親 = 子) as 根,
親,子,数量,
nvl(prior 数量,1) * 数量 as "数量×親の数量"
from 部品 a
start with not exists(select 1 from 部品 b
                       where b.子 = a.親)
connect by prior 子 = 親;

--■■■select句で相関サブクエリを使わない方法■■■
select b.親 as 根,
a.親,a.子,a.数量,a."数量×親の数量"
from (select aa.親,aa.子,aa.数量,
      nvl(prior 数量,1) * 数量 as "数量×親の数量"
      from 部品 aa
      start with not exists(select 1 from 部品 bb
                             where bb.子 = aa.親)
      connect by prior 子 = 親) a,
      (select distinct 親 from 部品 aa
        where not exists(select 1 from 部品 bb
                          where aa.親=bb.子)) b
where a.親 in (select c.親 from 部品 c
               start with c.親=b.親
               connect by prior c.子=c.親);

解説

Oracle8,8i,9i,10gと
Oracleのバージョンが上がるほど、単純なSQLになります。

7-27 階層問い合わせの根と葉を取得
9-15 階層問い合わせの根と葉も出力

---