假设有数据表结构如下,只有叶子节点有数据:
现在想统计处父节点合计数 ,如下:
使用CTE语法构建临时表如下:
2 | select 1 as id , null as parentid , '成本' as name , null as amount from dual union all |
3 | select 2,1 , '工资', null from dual union all |
4 | select 3,2 , '基本工资', 1000 from dual union all |
5 | select 4,2 , '奖金' , 200 from dual union all |
6 | select 5,1 , '保险' , 400 from dual |
2 | ---------------------- ---------------------- -------- ---------------------- |
解法一:
1 | select root_id,root_name,sum(amount) from (select connect_by_root(id) root_id,connect_by_root(name) root_name,amount from tmp where connect_by_isleaf=1 connectby prior id = parentid) group by root_id,root_name order by root_id; |
解法二(使用内查询方式):
1 | select id,parentid,name, (select sum(amount) from tmp a start with a.id=b.id connect by prior a.id=a.parentid ) sum_sal from tmp b order by 1; |
基本思路都是利用 connect by 子句自根节点/分支节点往叶子结点搜索,找出不同的根节点/分支节点到叶子节点的路径再求和,修改一下解法一的子查询并查看一下结果集:
1 | select connect_by_root(id) start_id,id leaf_id,amount from tmp where connect_by_isleaf=1 connect by prior id = parentid; |
01 | START_ID LEAF_ID AMOUNT |
02 | ---------- ---------- ---------- |
start_id 就是开始查找(不是start with)的节点id,leaf_id就是叶子节点的id,可以看到id=1的节点,也就是根节点的值等于三个叶子节点的值的总和,叶子节点的值是明确的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/3090/viewspace-703846/,如需转载,请注明出处,否则将追究法律责任。