create table #(cType int,Amount1 int,Amount2 int,Amount3 int)
insert # values(0 ,null,null, 30)
insert # values( 1, 20 , 10 , Null)
insert # values( 1 , 30 , 20 , Null)
insert # values( 0 , 50 , 30 , Null)
insert # values( 1 , 10 , 30 , Null)
insert # values( 0 , 10 , 30 , Null)
insert # values( 0 , 60 , 60 , Null)
declare @ int
select @=amount3 from # where amount1 is null and amount2 is null and amount3 is not nullupdate # set @=case when ctype=1 then @+amount1-amount2 else @ end,amount3=@select * from #go
drop table #
注:#为你的表
insert # values(0 ,null,null, 30)
insert # values( 1, 20 , 10 , Null)
insert # values( 1 , 30 , 20 , Null)
insert # values( 0 , 50 , 30 , Null)
insert # values( 1 , 10 , 30 , Null)
insert # values( 0 , 10 , 30 , Null)
insert # values( 0 , 60 , 60 , Null)
declare @ int
select @=amount3 from # where amount1 is null and amount2 is null and amount3 is not nullupdate # set @=case when ctype=1 then @+amount1-amount2 else @ end,amount3=@select * from #go
drop table #
注:#为你的表
----------- ----------- ----------- -----------
0 NULL NULL 30
1 20 10 40
1 30 20 50
0 50 30 50
1 10 30 30
0 10 30 30
0 60 60 30(所影响的行数为 7 行)有错吗??
不是这个意思。当cType='1'时,当前行的Amount3的值为它上一行的Amount3值+当前行的Amount1-当前行的Amount2;当cType='2'时,当前行的Amount3=上一行的Amount3
*/create table #(cType int,Amount1 int,Amount2 int,Amount3 int)
insert # values(2 ,null,null, 30)
insert # values( 1, 20 , 10 , Null)
insert # values( 1 , 30 , 20 , Null)
insert # values( 2 , 50 , 30 , Null)
insert # values( 1 , 10 , 30 , Null)
insert # values( 2 , 10 , 30 , Null)
insert # values( 2 , 60 , 60 , Null)
declare @ int
select @=amount3 from # where amount1 is null and amount2 is null and amount3 is not nullupdate # set @=case when ctype=1 then @+amount1-amount2 else @ end,amount3=@select * from #go
drop table #
结果:cType Amount1 Amount2 Amount3
----------- ----------- ----------- -----------
2 NULL NULL 30
1 20 10 40
1 30 20 50
2 50 30 50
1 10 30 30
2 10 30 30
2 60 60 30(所影响的行数为 7 行)
cID cType Amount1 Amount2 Amount3
1 0 NULL NULL 30
1 1 20 10 40
1 1 30 20 50
1 0 50 30 50
1 1 10 30 30
1 0 10 30 30
1 0 60 60 30
2 0 40
2 1 10 20 30
2 1 40 10 60
2 0 50 30 60
就是需要根据cID的不同分别来计算Amount3,该怎样写呢?
set @x=(select top 1 Amount3 from #t1)
update #T set @x=Amount3=(case when cType=0 then @x
else @x+Amount1-Amount2 end)
where Amount3 is NULLselect * from #T第二个问题有点难度
declare @x int
set @x=(select top 1 Amount3 from #T)
update #T set @x=Amount3=(case when cType=0 then @x
else @x+Amount1-Amount2 end)
where Amount3 is NULLselect * from #T2
drop table #T
go
create table #T(cID int,cType int,Amount1 int,Amount2 int,Amount3 int)
insert #T values(1,0 ,null,null, 30)
insert #T values( 1,1, 20 , 10 , Null)
insert #T values( 1,1 , 30 , 20 , Null)
insert #T values( 1,0 , 50 , 30 , Null)
insert #T values( 1,1 , 10 , 30 , Null)
insert #T values( 1,0 , 10 , 30 , Null)
insert #T values( 1,0 , 60 , 60 , Null)
insert #T values( 2,0 , Null , Null, 40)
insert #T values( 2,1 , 10 , 20 , Null)
insert #T values( 2,1 , 40 , 10 , Null)
insert #T values( 2,0 , 50 , 30 , Null)declare @x int
update #T set @x=(case when amount1 is null and amount2 is null and amount3 is not null then Amount3
else (case when cType=0 then @x else @x+Amount1-Amount2 end) end),amount3=@xselect * from #TcID cType Amount1 Amount2 Amount3
----------- ----------- ----------- ----------- -----------
1 0 NULL NULL 30
1 1 20 10 40
1 1 30 20 50
1 0 50 30 50
1 1 10 30 30
1 0 10 30 30
1 0 60 60 30
2 0 NULL NULL 40
2 1 10 20 30
2 1 40 10 60
2 0 50 30 60(11 row(s) affected)
declare @lastA3 int
set @lastA3=(select top 1 Amount3 from 临时表)
update 临时表
set Amount3=case when ctype=1 then isnull(@lastA3,0)+amount1-Amount2 else @lastA3
,@lastA3=Amount3
select * from 临时表