通过变量逐行更新实例

 

--测试代码

if object_id('[tb]') is not null

    drop table [tb]

go

create table tb([a] int,[b] int,[c] int)

insert tb([a],[b])

select 1,12 union all

select 1,13 union all

select 1,23 union all

select 0,14 union all

select 0,15 union all

select 1,16 union all

select 0,23 union all

select 0,22 union all

select 1,21 union all

select 1,12

GO

/*

select * from tb

-----------------------

a           b           c          

----------- ----------- -----------

1           12          NULL

1           13          NULL

1           23          NULL

0           14          NULL

0           15          NULL

1           16          NULL

0           23          NULL

0           22          NULL

1           21          NULL

1           12          NULL

 

(10 行受影响)

*/

 

/*

--更新为此效果的方法：

a           b           c          

----------- ----------- -----------

1           12          1

1           13          2

1           23          3

0           14          1

0           15          2

1           16          1

0           23          1

0           22          2

1           21          1

1           12          2

 

(10 行受影响)

*/

-- 法一:

declare @j int,@i int

update tb set

    c = @j,

    @j = case when @i = a then @j +1 else 1 end,

    @i = a

 

select * from tb

 

GO

--法二:

declare @j int,@i int

update tb set

    c = @j,

    @j = case when @i <> a then 1 else isnull(@j,0)+1 end,

    @i=a

 

select * from tb

 

GO

 

 

/*

--更新为此效果的方法

a           b           c

----------- ----------- -----------

1           12          1

1           13          1

1           23          1

0           14          2

0           15          2

1           16          3

0           23          4

0           22          4

1           21          5

1           12          5

 

(10 行受影响)

 

*/

 

-- 法一:

declare @j int,@i int

update tb set

    c = @j,

    @j = case when @i = a then @j else isnull(@j,0)+1 end,

    @i = a

 

select * from tb

 

GO

 

--法二:

declare @j int,@i int

update tb set

    c = @j,

    @j = case when @i <> a then @j+1 else isnull(@j,1) end,

    @i=a

 

select * from tb

 

GO

 

 

 

-------------------------------------------

--将下表的null值用id相同的非null值来填充:

if object_id('[tb]') is not null

    drop table [tb]

go

create table tb(id int,value varchar(2))

insert tb

select 1,'a' union all

select 1,'a' union all

select 1,null union all

select 2,'b' union all

select 2,'b' union all

select 2,null

GO

 

-->sql查询如下:

declare @s varchar(20);

update tb set

    @s = isnull(value,@s),

    value = @s;

   

select * from tb;

/*

 

id          value

----------- -----

1           a

1           a

1           a

2           b

2           b

2           b

 

（所影响的行数为6 行）

*/