简单相加问题100分，最好一句update搞定
--#t和#t1 id相同时#t的valF加上#t1的addnum下一条数据的varT等于前一条的valF
--当#t1里面addnum变化时代码也要可用
create table #t (id int ,valF int,varT int)
insert #t
select 1,0,50 union
select 2,0,0 union
select 3,0,0 union
select 4,0,0 union
select 5,0,0
create table #t1(id int,addnum int)
insert #t1
select 1,10 union
select 2,12 union
select 3,13 union
select 4,17 union
select 5,30
select * from #t
select * from #t1
--要求结果
1,60,50
2,72,60
3,85,72
4,102,85
5,132,102
[解决办法]
create table #t (id int ,valF int,varT int)
insert #t
select 1,0,50 union
select 2,0,0 union
select 3,0,0 union
select 4,0,0 union
select 5,0,0
create table #t1(id int,addnum int)
insert #t1
select 1,10 union
select 2,12 union
select 3,13 union
select 4,17 union
select 5,30
go
select
a.id,
valF=sum(b.varT)+sum(c.addnum),
valT=sum(b.varT)+sum(case when c.id <a.id then c.addnum else 0 end)
from
#t a,#t b,#t1 c
where
a.id> =b.id and b.id=c.id
group by
a.id
go
/*
id valF valT
----------- ----------- -----------
1 60 50
2 72 60
3 85 72
4 102 85
5 132 102
*/
drop table #t,#t1
go
[解决办法]
--更新的语句
create table #t (id int ,valF int,varT int)
insert #t
select 1,0,50 union
select 2,0,0 union
select 3,0,0 union
select 4,0,0 union
select 5,0,0
create table #t1(id int,addnum int)
insert #t1
select 1,10 union
select 2,12 union
select 3,13 union
select 4,17 union
select 5,30
go
update a
set
valF=(select sum(b.varT+c.addnum) from #t b,#t1 c where b.id=c.id and b.id <=a.id),
varT=(select sum(b.varT+c.addnum) from #t b,#t1 c where b.id=c.id and b.id <=a.id)-d.addnum
from
#t a,#t1 d
where
a.id=d.id
go
select * from #t
/*
id valF varT
----------- ----------- -----------
1 60 50
2 72 60
3 85 72
4 102 85
5 132 102
*/
drop table #t,#t1
go
