主要就是想把相同条件的数据按照同样的规则更新。 例如x,y 要变为x ,y 不是 x , y a 1 a 1 a 1 a 2 a 3 a 3 a 3 a 6 a 6 b 1 b 1 a 7 b 2 b 3 a 9 b 3 b 6 a 12
DECLARE @X CHAR(1) ,@Y INT
SET @Y=0 UPDATE tablename SET @Y=CASE WHEN @X=X THEN @Y+Y ELSE Y END ,@X=CASE WHEN COALESCE(@X,'')=X THEN @X ELSE X END ,Y=@Y FROM tablename
alter table tablename add id int identity(1,1)update tablename set y=(select sum(y) from tablename where x=a.x and id<=a.id) from tablename aalter table tablename drop column id
--更新处理 declare @x varchar(1),@i1 int,@i2 intset @i2=0 update 表 set @i1=case @x when x then @i2+y else y end ,@i2=@i1 ,y=@i1,@x=x
--测试数据 declare @t table(x varchar(1),y int) insert into @t select 'a',1 union all select 'a',2 union all select 'a',3 union all select 'b',1 union all select 'b',2 union all select 'b',3--更新处理 declare @x varchar(1),@i1 int,@i2 intset @i2=0 update @t set @i1=case @x when x then @i2+y else y end ,@i2=@i1 ,y=@i1,@x=x--显示处理结果 select * from @t/*--测试结果 x y ---- ----------- a 1 a 3 a 6 b 1 b 3 b 6(所影响的行数为 6 行) --*/
例如x,y 要变为x ,y 不是 x , y
a 1 a 1 a 1
a 2 a 3 a 3
a 3 a 6 a 6
b 1 b 1 a 7
b 2 b 3 a 9
b 3 b 6 a 12
,@Y INT
SET @Y=0
UPDATE tablename
SET @Y=CASE WHEN @X=X THEN @Y+Y ELSE Y END
,@X=CASE WHEN COALESCE(@X,'')=X THEN @X ELSE X END
,Y=@Y
FROM tablename
set y=(select sum(y) from tablename where x=a.x and id<=a.id)
from tablename aalter table tablename drop column id
declare @x varchar(1),@i1 int,@i2 intset @i2=0
update 表 set @i1=case @x when x then @i2+y else y end
,@i2=@i1
,y=@i1,@x=x
declare @t table(x varchar(1),y int)
insert into @t
select 'a',1
union all select 'a',2
union all select 'a',3
union all select 'b',1
union all select 'b',2
union all select 'b',3--更新处理
declare @x varchar(1),@i1 int,@i2 intset @i2=0
update @t set @i1=case @x when x then @i2+y else y end
,@i2=@i1
,y=@i1,@x=x--显示处理结果
select * from @t/*--测试结果
x y
---- -----------
a 1
a 3
a 6
b 1
b 3
b 6(所影响的行数为 6 行)
--*/