原文地址在这里:【难题】如何将叶子节点的数据合计到父节点,原文已经不能回复了,在这里写下解法。
题目简单描述如下:
有数据表结构如下,只有叶子节点有数据:
id parentId name amount
1 成本
2 1 工资
3 2 基本工资 1000
4 2 奖金 200
5 1 保险 400
现在想统计处父节点合计数 ,如下:
id name amount
1 成本 1600 //2 + 5
2 工资 1200 //3 + 4
3 基本工资 1000
4 奖金 200
5 保险 400
作者要求是Oracle数据库。
由于功力不够求助了ITPUB上的大牛,也得到了精妙的回复,在这里记录一下:
为节省篇幅,使用CTE语法构建临时表,不再建表了:
with tmp as (
select 1 as id , null as parentid , '成本' as name , null as amount from dual union all
select 2,1 , '工资', null from dual union all
select 3,2 , '基本工资', 1000 from dual union all
select 4,2 , '奖金' , 200 from dual union all
select 5,1 , '保险' , 400 from dual
)
select * from tmp
ID PARENTID NAME AMOUNT
---------------------- ---------------------- -------- ----------------------
1 成本
2 1 工资
3 2 基本工资 1000
4 2 奖金 200
5 1 保险 400
首先是来自2楼newkid的解法:
SELECT root_id,SUM(amount)
FROM (
select CONNECT_BY_ROOT(id) root_id,amount
from tmp
WHERE CONNECT_BY_ISLEAF=1
CONNECT BY PRIOR id = parentid
)
GROUP BY root_id;
然后是3楼dingjun123的解法:
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 子句自根节点/分支节点往叶子结点搜索,找出不同的根节点/分支节点到叶子节点的路径再求和,修改一下newkid的子查询并查看一下结果集就比较明了:
select CONNECT_BY_ROOT(id) start_id,id leaf_id,amount
from tmp
WHERE CONNECT_BY_ISLEAF=1
CONNECT BY PRIOR id = parentid
START_ID LEAF_ID AMOUNT
---------- ---------- ----------
1 3 1000
1 4 200
1 5 400
2 3 1000
2 4 200
3 3 1000
4 4 200
5 5 400
start_id 就是开始查找(不是start with)的节点id,leaf_id就是叶子节点的id,用图来帮助分析:
可以看到id=1的节点,也就是根节点的值等于三个叶子节点的值的总和,叶子节点的值是明确的。
下一篇将会使用SQL Server递归CTE语句对该问题求解,请留意。