現(xiàn)有一表記錄了三層樹(shù)形結(jié)構(gòu)的數(shù)據(jù)， 現(xiàn)想返回每個(gè)葉子節(jié)點(diǎn)名稱(chēng)以及每個(gè)葉子節(jié)點(diǎn)權(quán)重與其父節(jié)點(diǎn)權(quán)重的乘積，請(qǐng)問(wèn)各位有什么好方法嗎？？？？
1 回答
猛跑小豬
TA貢獻(xiàn)1858條經(jīng)驗(yàn) 獲得超8個(gè)贊
Oracle tree計(jì)算葉子節(jié)點(diǎn)到根節(jié)點(diǎn)的乘積
1.//有下面一棵二叉樹(shù)，轉(zhuǎn)換為表結(jié)構(gòu):
2.parent_id child_id weight
3.------ ------- ------
4.a b 2
5.b c 3
6.c d 4
7.b e 7
8.c f 2
9.//計(jì)算葉子節(jié)點(diǎn)到根節(jié)點(diǎn)之間邊的權(quán)值的乘積: 10.leaf weight
11.---- ------
12.d 24
13.e 14
14.f 12
15.//數(shù)據(jù) 16.create table tree (parent_id varchar2(10),child_id varchar2(10),weight number(2));
17.insert into tree values('a','b',2); 18.insert into tree values('b','c',3); 19.insert into tree values('c','d',4); 20.insert into tree values('b','e',7); 21.insert into tree values('c','f',2); 22.//創(chuàng)建一個(gè)函數(shù)實(shí)現(xiàn)求字串乘積(動(dòng)態(tài)SQL) 23.create or replace function func_tree(str in varchar2) 24.return number 25.as 26. num number;
27.begin
28. execute immediate 'select '||str||' from dual' into num; 29. return num; 30.end func_tree;
31.//sql代碼: 32.select child_id, func_tree(substr(sys_connect_by_path(weight, '*'), 2)) weight 33.from tree t
34.where connect_by_isleaf = 1
35.start with not exists (select 1 from tree where t.parent_id=child_id)
36.connect by prior child_id = parent_id
37.order by child_id;
38.//結(jié)果: 39.CHILD_ID WEIGHT
40.---------- ----------
41.d 24
42.e 14
43.f 12
- 1 回答
- 0 關(guān)注
- 249 瀏覽
添加回答
舉報(bào)
0/150
提交
取消