猛跑小猪
Oracle tree计算叶子节点到根节点的乘积 1.//有下面一棵二叉树，转换为表结构: 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.//计算叶子节点到根节点之间边的权值的乘积: 10.leaf weight 11.---- ------ 12.d 24 13.e 14 14.f 12 15.//数据 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.//创建一个函数实现求字串乘积(动态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.//结果: 39.CHILD_ID WEIGHT 40.---------- ---------- 41.d 24 42.e 14 43.f 12