sql语句实现列值逐级累加(仅影响上一级)

Question

模型如下:
日期       树Id                          金额         类别
200607 000190                        0          4
200607 000190000001               0          4
200607 000190000001000034               0          3
200607 000190000001000034000004      0          2
200607 000190000001000034000004000001  50000 1
200607 000190000001000034000004000002  50000 1
200607 000190000001000034000004000003  50000 1
200607 000190000001000034000005        0     1
200607 000190000001000034000005000001  50000 1要求:
日期       树Id                          金额         类别
200607 000190                        200000 4
200607 000190000001               200000 4
200607 000190000001000034               200000 3
200607 000190000001000034000004      150000       2
200607 000190000001000034000004000001   50000 1
200607 000190000001000034000004000002   50000 1
200607 000190000001000034000004000003   50000 1
200607 000190000001000034000005         50000     1
200607 000190000001000034000005000001   50000 1

Answer 1

按照树Id,累加上级,但是也要考虑兄弟极.
日期       树Id                          金额         类别
200607 000190                        0          4
200607 000190000001               0          4
200607 000190000001000034               0          3
200607 000190000001000034000004      0          2
200607 000190000001000034000004000001  50000 1
200607 000190000001000034000004000002  50000 1
200607 000190000001000034000004000003  50000 1
200607 000190000001000034000005        0     2
200607 000190000001000034000005000001  50000 1

Answer 2

模型如下:
日期                           树Id                        金额      类别
200607               000190                                 0         4
200607               000190000001                           0         4
200607               000190000001000034                     0         3
200607               000190000001000034000004               0         2
200607               000190000001000034000004000001         50000     1
200607               000190000001000034000004000002         50000     1
200607               000190000001000034000004000003         50000     1
200607               000190000001000034000005               0         1
200607               000190000001000034000005000001         50000     1要求:
日期                           树Id                        金额      类别
200607               000190                                 200000    4
200607               000190000001                           200000    4
200607               000190000001000034                     200000    3
200607               000190000001000034000004               150000    2
200607               000190000001000034000004000001         50000     1
200607               000190000001000034000004000002         50000     1
200607               000190000001000034000004000003         50000     1
200607               000190000001000034000005               50000     1
200607               000190000001000034000005000001         50000     1

Answer 3

这是内核累加的sql,之后关联'类别'字段就行了，我已经试过了，
主要算法在“trim(b.树Id) = substr(trim(a.树Id(+)),1, b.t_len)”上。
你试试：
select c.日期,trim(c.树Id),sum(c.金额)
  from (select b.日期,b.树Id，a.金额
          from (select 日期 ,树Id, 金额, 类别
                  from 表 ) a,
               (select distinct 日期 ,树Id,length(trim(树Id)) as t_len
                  from 表) b
         where b.日期=a.日期(+)
         and trim(b.树Id) = substr(trim(a.树Id(+)),1, b.t_len)
        ) c
 group by c.日期,trim(c.树Id)