create table IDTable(
ID number(2),
PrevID number(2),
Val number(2));
insert into IDTable values( 1,null,10);
insert into IDTable values( 2, 1,20);
insert into IDTable values( 3, 1,30);
insert into IDTable values( 4, 2,15);
insert into IDTable values( 5, 4,40);
insert into IDTable values( 6, 4,50);
insert into IDTable values( 7, 5,60);
insert into IDTable values(90,null,10);
insert into IDTable values(91, 90,20);
commit;
SQL
--■■■connect_by_rootを使う方法(10g以降)■■■
select RootID as ID,
RootPrevID as PrevID,
max(decode(lv,1,Val)) as Val,
max(lv) as "Level",
sum(Val) as SumVal
from (select
connect_by_root ID as RootID,
connect_by_root PrevID as RootPrevID,
ID,Val,Level as lv
from IDTable
connect by prior PrevID = ID)
group by RootID,RootPrevID
having max(case when ID = 1 then 1 else 0 end) = 1
order by RootID,RootPrevID;
--■■■相関サブクエリを使う方法■■■
select ID,PrevID,Val,Level,
(select sum(b.Val)
from IDTable b
Start With b.RowID = a.RowID
connect by ID = prior PrevID) as SumVal
from IDTable a
Start With ID = 1
connect by prior ID = PrevID
order by ID,PrevID;
解説
connect_by_rootを使う方法では、
データ構造が、
親が、最大でも1つなこと(逆に探索しても枝分かれが発生しない)
を使ってます
類似のSQLとして4-12 階層の総積を取得があります