求救，将上月累计数变为本期的上月数，在线等ing...............
计算收款时要将上月的累计未收变成次月的上月未收
本表计算公式如下：
本月未收＝本月应收－本月实收
累计未收＝本月未收＋上月未收
月份本月应收本月实收本月未收上月未收累计未收
1200001001990019900
2522526-41990019896
316311516161989621512
413251556-2312151221281
551312541-12028212819253
6231212-118992538064
7213255411-532798064-45215
896856659020-45215-36195
912652162-897-36195-37092
1032511563095-37092-33997
11585515114344-33997-29653
1289415653-14759-29653-44412
各位，等着急用，支个招，明天要交差了。
[解决办法]
初始數據是怎樣的？
[解决办法]
create table tt(月份 int,本月应收 int,本月实收 int)
insert into tt select 1,20000,100
union all select 2,522,526
union all select 3,1631,15
union all select 4,1325,1556
union all select 5,513,12541
union all select 6,23,1212
union all select 7,2132,55411
union all select 8,9685,665
union all select 9,1265,2162
union all select 10,3251,156
union all select 11,5855,1511
union all select 12,894,15653
select *,(本月应收-本月实收)本月未收,
(select sum(本月应收-本月实收) from tt b where b.月份 <a.月份)上月未收,
(select sum(本月应收-本月实收) from tt b where b.月份 <=a.月份)累计未收
from tt a
月份本月应收本月实收本月未收上月未收累计未收
1200001001990019900
2522526-41990019896
316311516161989621512
413251556-2312151221281
551312541-12028212819253
6231212-118992538064
7213255411-532798064-45215
896856659020-45215-36195
912652162-897-36195-37092
1032511563095-37092-33997
11585515114344-33997-29653
1289415653 -14759-29653-44412
[解决办法]
CREATE TABLE tb
(
月份 INT,
本月应收 INT,
本月实收 INT
)
INSERT INTO tb
SELECT 1,20000,100 UNION ALL
SELECT 2,522,526 UNION ALL
SELECT 3,1631,15 UNION ALL
SELECT 4,1325,1556 UNION ALL
SELECT 5,513,12541 UNION ALL
SELECT 6,23,1212 UNION ALL
SELECT 7,2132,55411 UNION ALL
SELECT 8,9685,665 UNION ALL
SELECT 9,1265,2162 UNION ALL
SELECT 10,3251,156 UNION ALL
SELECT 11,5855,1511 UNION ALL
SELECT 12,894,15653
--执行
SELECT 月份,本月应收,本月实收,本月应收-本月实收 本月未收, ISNULL((SELECT 本月应收-本月实收 FROM tb WHERE 月份=A.月份-1),0) 上月未收,(本月应收-本月实收+ISNULL((SELECT 本月应收-本月实收 FROM tb WHERE 月份=A.月份-1),0)) 累计未收 FROM tb A
--结果
月份 本月应收 本月实收 本月未收 上月未收 累计未收
----------- ----------- ----------- ----------- ----------- -----------
1 20000 100 19900 0 19900
2 522 526 -4 19900 19896
3 1631 15 1616 -4 1612
4 1325 1556 -231 1616 1385
5 513 12541 -12028 -231 -12259
6 23 1212 -1189 -12028 -13217
7 2132 55411 -53279 -1189 -54468
8 9685 665 9020 -53279 -44259
9 1265 2162 -897 9020 8123
10 3251 156 3095 -897 2198
11 5855 1511 4344 3095 7439
12 894 15653 -14759 4344 -10415
(12 行受影响)
