假设有数据表结构如下，只有叶子节点有数据：
现在想统计处父节点合计数 ，如下：
使用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/，如需转载，请注明出处，否则将追究法律责任。
