求根据当前月份计算累积金额,谢谢
部门预算表ACTMK
MK002(年度),MK005(月份),MK003(会计科目),MK004(部门),MK006(预算金额)
2013 01 660202 30101 8000
2013 02 660202 30101 2000
2013 03 660202 30101 3000
2013 04 660202 30101 1000
2013 05 660202 30101 4000
2013 06 660202 30101 5000
2013 07 660202 30101 700
2013 08 660202 30101 500
2013 09 660202 30101 600
2013 10 660202 30101 700
2013 11 660202 30101 800
2013 12 660202 30101 9000
......表里还有其他的科目和不同的部门存在
求实现:比如 部门为30101,会计科目为660202 的四月的数据
MK002(年度),MK005(月份),MK003(会计科目),MK004(部门),MK006(预算金额),AAA(截止当前月份的累积预算金额)
2013 04 660202 30101 1000 8000+2000+3000+1000问题是如何计算截止当前月份的累积预算金额
谢谢
[解决办法]
with tb(a,b) as(
select 10, '01' union all
select 20, '02' union all
select 30, '03' union all
select 40, '04' union all
select 50, '05' union all
select 60, '06' union all
select 70, '07'
)
select sum(a.a),b.b from tb a, tb b where a.b<=b.b
group by b.b
/*
a b
10 01
30 02
60 03
10004
15005
21006
28007
*/
select A.MK002 ,
A.MK005 ,
A.MK003 ,
A.MK004 ,
A.MK006 ,
SUM(t.mk006) AS aaa
FROM TB A
INNER JOIN TB T ON T.mk002 = A.mk002 AND T.mk003=a.mk003 AND T.mk004=a.mk004
AND CONVERT(INT, T.mk005) <= CONVERT(INT, a.mk005)
GROUP BY A.MK002 ,
A.MK005 ,
A.MK003 ,
A.MK004 ,
A.MK006
/*
MK002MK005MK003MK004MK006aaa
2013016602023010180008000
20130266020230101200010000
20130366020230101300013000
20130466020230101100014000
20130566020230101400018000
20130666020230101500023000
2013076602023010170023700
2013086602023010150024200
2013096602023010160024800
2013106602023010170025500
2013116602023010180026300
20131266020230101900035300
