有数据表结构如下, 只有叶子节点有数据id parentId name amount1 成本 2 1 工资 3 2 基本工资 1000 4 2 奖金 2005 1 保险 400现在想统计处父节点合计数 ,如下:1 成本 1600 //2 + 5
2 工资 1200 //3 + 43 基本工资 1000 4 奖金 2005 保险 400
请高手支招? 谢谢 急~~~~~~~~
2 工资 1200 //3 + 43 基本工资 1000 4 奖金 2005 保险 400
请高手支招? 谢谢 急~~~~~~~~
---------- ---------- ------------------------------ ----------
1 成本
2 1 工资
3 2 基本工资 1000
4 2 奖金 200
5 1 保险 400SQL> select sum(amount) from rmb t
2 start with id=&id
3 connect by prior id=parentid
4 /SUM(AMOUNT)
-----------
1600
select sum(nvl(amount,0)) from tab start with id=1 connect by prior id = parentid
Result number;
begin select sum(amount)
into result
from a1
start with id = aid
connect by prior id = pid;
return(Result);
end a;select id,a(id),name from a1;
1 1 1600 成本
2 2 1200 工资
3 3 1000 基本工资
4 4 200 奖金
5 5 400 保险
with mcost as (
select '1' id, null parentid, '成本' name, null amount from dual union all
select '2' id, '1' parentid, '工资' name, null amount from dual union all
select '3' id, '2' parentid, '基本工资' name, 1000 amount from dual union all
select '4' id, '2' parentid, '奖金' name, 200 amount from dual union all
select '5' id, '1' parentid, '保险' name, 400 amount from dual)
SELECT b.id, b.name, a.amount
FROM (SELECT rootid, SUM(amount) amount
FROM (SELECT t.*, connect_by_root id rootid
FROM mcost t
START WITH t.id IN (SELECT id FROM mcost)
CONNECT BY PRIOR t.id = t.parentid)
GROUP BY rootid) a,
mcost b
WHERE b.id = a.rootid;
SELECT b.id, b.name, a.amount
FROM (SELECT rootid, SUM(amount) amount
FROM (SELECT id,
amount,
substr(sys_connect_by_path(t.id, ','),
2,
instr(sys_connect_by_path(t.id, ',') || ',', ',', 2) - 2) rootid
FROM mcost t
START WITH t.id IN (SELECT id FROM mcost)
CONNECT BY PRIOR t.id = t.parentid)
GROUP BY rootid) a,
mcost b
WHERE b.id = a.rootid;