34,591
社区成员
发帖
与我相关
我的任务
分享
;with cte as(
select id=row_number()over(partition by 合同编号 order by 本期收款 asc),* from tb
)
select t.*,累计收款=(select sum(本期收款) from cte where 合同编号 = t.合同编号 and id <= t.id) from cte t
create table tb (合同编号 varchar(6),本期收款 numeric(5,2))
insert into tb
select '000001',200.00 union all
select '000001',350.00 union all
select '000001',450.00 union all
select '000001',800.00 union all
select '000002',200.00 union all
select '000002',350.00 union all
select '000002',450.00 union all
select '000002',800.00 union all
select '000003',200.00 union all
select '000003',350.00 union all
select '000003',450.00 union all
select '000003',800.00
go
select t.*,累计收款=(select sum(本期收款) from tb where 合同编号 = t.合同编号 and 本期收款 <= t.本期收款) from tb t
drop table tb
/*
合同编号 本期收款 累计收款
------ ------- ----------------------------------------
000001 200.00 200.00
000001 350.00 550.00
000001 450.00 1000.00
000001 800.00 1800.00
000002 200.00 200.00
000002 350.00 550.00
000002 450.00 1000.00
000002 800.00 1800.00
000003 200.00 200.00
000003 350.00 550.00
000003 450.00 1000.00
000003 800.00 1800.00
(所影响的行数为 12 行)
*/
关键是自动增量id列,用于比较并逐条统计!
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (合同编号 varchar(6),本期收款 numeric(5,2))
insert into #tb
select '000001',200.00 union all
select '000001',350.00 union all
select '000001',450.00 union all
select '000001',800.00 union all
select '000002',200.00 union all
select '000002',350.00 union all
select '000002',450.00 union all
select '000002',800.00 union all
select '000003',200.00 union all
select '000003',350.00 union all
select '000003',450.00 union all
select '000003',800.00
alter table #tb add id int identity(1,1)
select 合同编号,本期收款,
累计收款=(select sum(本期收款) from #tb where 合同编号=t.合同编号 and id<=t.id)
from #tb t
合同编号 本期收款 累计收款
------ --------------------------------------- ---------------------------------------
000001 200.00 200.00
000001 350.00 550.00
000001 450.00 1000.00
000001 800.00 1800.00
000002 200.00 200.00
000002 350.00 550.00
000002 450.00 1000.00
000002 800.00 1800.00
000003 200.00 200.00
000003 350.00 550.00
000003 450.00 1000.00
000003 800.00 1800.00
(12 行受影响)