【难题】如何将叶子节点的数据合计到父节点

[tds__609]

有数据表结构如下， 只有叶子节点有数据

id parentId name amount

1 成本

2 1 工资

3 2 基本工资 1000

4 2 奖金 200

5 1 保险 400

现在想统计处父节点合计数 ，如下：

1 成本 1600 //2 + 5

2 工资 1200 //3 + 4

3 基本工资 1000

4 奖金 200

5 保险 400
请高手支招？ 谢谢 急~~~~~~~~

[iqlife]

SQL> select * from rmb;

        ID   PARENTID NAME                               AMOUNT
---------- ---------- ------------------------------ ----------
         1            成本                           
         2          1 工资                           
         3          2 基本工资                             1000
         4          2 奖金                                  200
         5          1 保险                                  400

SQL> select sum(amount) from rmb t
  2  start with id=&id
  3  connect by prior id=parentid
  4  /

SUM(AMOUNT)
-----------
       1600

[zhangwonderful]

参考sql:
select sum(nvl(amount,0)) from tab

start with id=1

connect by prior id = parentid

[tds__609]

不是太正确啊 工资节点没统计出来啊

[iqlife]

你想要什么样子的结果,不是一个总数?

[jdsnhan]

10分钟之内没写出一句能搞定的sql，变通用函数就简单了。

create or replace function a(aid in number) return number is
  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	保险

[tangren]

--在10g上可用
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;

[tangren]

--9i可用
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;

[fromdw]

[Quote=引用 6 楼 tangren 的回复:]
SQL code--在10g上可用with mcostas (select'1' id,null parentid,'成本' name,null amountfrom dualunionallselect'2' id,'1' parentid,'工资' name,null amountfrom dualunionallselect'3' id,'2' parentid,'基本工资' name,1000 amountfrom dualunionallselect'4' id,'2' parentid,'奖金' name,200 amountfrom dualunionallselect'5' id,'1' parentid,'保险' name,400 amountfrom dual)SELECT b.id, b.name, a.amountFROM (SELECT rootid,SUM(amount) amountFROM (SELECT t.*, connect_by_root id rootidFROM mcost t
STARTWITH t.idIN (SELECT idFROM mcost)
CONNECTBY PRIOR t.id= t.parentid)GROUPBY rootid) a,
mcost bWHERE b.id= a.rootid;
[/Quote]


很好，很强大

[niuhongze]

正好遇到这样的问题，学习！