oracle tree计算叶子节点到根节点的乘积

//有下面一棵二叉树，转换为表结构: parent_id child_id weight ------ ------- ------ a b 2 b c 3 c d 4 b e 7 c f 2 //计算叶子节点到根节点之间边的权值的乘积: leaf weight ---- ------ d 24 e 14 f 12 //数据 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); //创建一个函数实现求字串乘积(动态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; //结果: CHILD_ID WEIGHT ---------- ---------- d 24 e 14 f 12

原帖:http://topic.youkuaiyun.com/u/20110412/11/d9d83f58-e388-4280-ae59-f5668dc028bd.html

学习sys_connect_by_path:http://space.itpub.net/519536/viewspace-624099