Oracle tree计算叶子节点到根节点的乘积
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
评论暂时关闭