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

wzt10 2006-10-16 04:39:06

模型如下:
日期树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

...全文

528 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

wzt10 2007-03-02

打赏
举报

小李木耳 2007-03-02

打赏
举报

追加说明：
数据量大的时候可能比较缓慢，你再追加一些优化方面的写法/语句。

小李木耳 2007-03-02

打赏
举报

这是内核累加的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)

Eric_1999 2006-10-16

打赏
举报

终于明白了，吃完饭再来帮你。:-P

wzt10 2006-10-16

打赏
举报

日期树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 2
200607 000190000001000034000005000001 50000 1

wzt10 2006-10-16

打赏
举报

按照树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

wzt10 2006-10-16