if object_id('[tb]') is not null drop table [tb] 
go 
create table [tb]([value1] int,[value2] int) 
insert [tb] 
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 

alter table tb add value3 int
declare @i int,@j int
update tb set value3=@j,@j=case when @i <> value1 then 1 else isnull(@j,0)+1 end,@i=value1
select * from tb

alter table tb drop column value3
/*
value1      value2      value3
----------- ----------- -----------
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 @i int,@value1 int
update col3 = @I,@i = case when @value1 = value1 then @i +1 else 0 end,@value1 = value1  


------解决方案--------------------
SQL code
if object_id('[tb]') is not null drop table [tb]  
 go  
 create table [tb]([value1] int,[value2] int)  
 insert [tb]  
 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  
 alter table tb add  id int 
 declare @n int,@k int,@flag int 
 set @flag=1 
 set @n=0 
 set @k=0 
 update tb 
 sET  ID=@n, 
 @flag=case when value1=@k then 1 else 0 end,  
   @k=value1, 
   @n= case when @flag=1 then @n+1 else 1 end 
  select * from tb  
  /* 
  (10 行受影响) 
 value1    value2    id 
 ----------- ----------- ----------- 
 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 
  
  */


------解决方案--------------------
SQL code
if object_id('[tb]') is not null drop table [tb] 
go 
create tab