统计累积金额 论坛好人帮忙 谢谢
本帖最后由 erpcsu 于 2013-05-03 15:23:06 编辑 费用表ACTTBF
MK002(年),MK005(月),MK003(科目),MK004(部门),TB007(金额)
201301660202 30101 11398.38
201301660203 30101 800.00
201301660221 30101 .00
201301660222 30101 .00
201301660223 30101 .00
201301660224 30101 .00
201301660225 30101 .00
201301660231 30101 .00
201301660238 30101 .00
201302660202 30101 11887.37
201302660203 30101 800.00
201302660221 30101 .00
201302660222 30101 .00
201302660223 30101 .00
201302660224 30101 .00
201302660225 30101 89.76
201302660231 30101 .00
201302660238 30101 .00
201303660202 30101 9819.92
201303660203 30101 800.00
201303660221 30101 .00
201303660222 30101 .00
201303660223 30101 .00
201303660224 30101 .00
201303660225 30101 .00
201303660231 30101 .00
201303660238 30101 .00
......表里还有其他部门的数据
需要增加一列显示截止当前年月的同一个部门、同一个科目累计额的金额
实现如下
MK002(年),MK005(月),MK003(科目),MK004(部门),TB007(金额)，TOTAL(截止当前年、月，同一科目、同一部门的累积金额)
201301660202 30101 11398.38 11398.38
201301660203 30101 800.00 800
201301660221 30101 .00 0
201301660222 30101 .00 0
201301660223 30101 .00 0
201301660224 30101 .00 0
201301660225 30101 .00 0
201301660231 30101 .00 0
201301660238 30101 .00 0
201302660202 30101 11887.37 11398.38+11887.37
201302660203 30101 800.00 800+800
201302660221 30101 .00 0
201302660222 30101 .00 0
201302660223 30101 .00 0
201302660224 30101 .00 0
201302660225 30101 89.76 89.76
201302660231 30101 .00 0
201302660238 30101 .00 0
201303660202 30101 9819.92 11398.38+11887.37+9819.92
201303660203 30101 800.00 800+800+800
201303660221 30101 .00 0
201303660222 30101 .00 0
201303660223 30101 .00 0
201303660224 30101 .00 0
201303660225 30101 .00 0+89.76
201303660231 30101 .00 0
201303660238 30101 .00 0
非常感谢
[解决办法]
select a.MK002,
a.MK005,
a.MK003,
sum(a.TB007) 'TB007',
(select sum(b.TB007) from ACTTBF b
where b.MK003=a.MK003 and b.MK004=a.MK004
and cast(b.MK002+b.MK005+'01' as date)
<=cast(a.MK002+a.MK005+'01' as date)) 'TOTAL'
from ACTTBF a
group by a.MK002,a.MK005,a.MK003
