//有下面一棵二叉樹(shù),轉(zhuǎn)換為表結(jié)構(gòu):
parent_id child_id weight
------ ------- ------
a b 2
b c 3
c d 4
b e 7
c f 2
//計(jì)算葉子節(jié)點(diǎn)到根節(jié)點(diǎn)之間邊的權(quán)值的乘積:
leaf weight
---- ------
d 24
e 14
f 12
//數(shù)據(jù)
create table tree (parent_id varchar2(10),child_id varchar2(10),weight number(2));
insert into tree values('a','b',2);
insert into tree values('b','c',3);
insert into tree values('c','d',4);
insert into tree values('b','e',7);
insert into tree values('c','f',2);
//創(chuàng)建一個(gè)函數(shù)實(shí)現(xiàn)求字串乘積(動(dòng)態(tài)SQL)
create or replace function func_tree(str in varchar2)
return number
as
num number;
begin
execute immediate 'select '||str||' from dual' into num;
return num;
end func_tree;
//sql代碼:
select child_id, func_tree(substr(sys_connect_by_path(weight, '*'), 2)) weight
from tree t
where connect_by_isleaf = 1
start with not exists (select 1 from tree where t.parent_id=child_id)
connect by prior child_id = parent_id
order by child_id;
//結(jié)果:
CHILD_ID WEIGHT
---------- ----------
d 24
e 14
f 12
本文出自:億恩科技【mszdt.com】
服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)!虛擬主機(jī)域名注冊(cè)頂級(jí)提供商!15年品質(zhì)保障!--億恩科技[ENKJ.COM]
|