create table Busyo(name,oya,采配) as
select '織田信秀',null ,78 from dual union
select '織田信長','織田信秀',95 from dual union
select '織田信行','織田信秀',43 from dual union
select '織田信忠','織田信長',70 from dual union
select '織田信考','織田信長',56 from dual union
select '織田秀信','織田信忠',31 from dual union
select '徳川家康',null ,97 from dual union
select '徳川秀忠','徳川家康',71 from dual union
select '松平忠吉','徳川家康',68 from dual union
select '徳川家光','徳川秀忠',84 from dual;
--■■■connect_by_rootを使う方法(10g以降)■■■
select name,oya,采配,connect_by_root 采配 as root采配
from Busyo
Start With oya is null
connect by prior name = oya;
--■■■sys_connect_by_pathを使う方法■■■
select name,oya,采配,
to_number(substr(sys_connect_by_path(to_char(采配),','),2,
decode(instr(substr(sys_connect_by_path(to_char(采配),','),2),','),
0,Length(substr(sys_connect_by_path(to_char(采配),','),2)),
instr(substr(sys_connect_by_path(to_char(采配),','),2),',')-1))) as Root采配
from Busyo
Start With oya is null
connect by prior name = oya;
--■■■8iEEでも動きそうな方法■■■
select name,oya,采配,
First_Value(采配) over(partition by PartID order by SortKey) as Root采配
from (select name,oya,采配,SortKey,
sum(willSum) over(order by SortKey) as PartID
from (select name,oya,采配,decode(Level,1,1,0) as willSum,
RowNum as SortKey
from Busyo
Start With oya is null
connect by prior name = oya));