以前看树哥弄过,忘了,望前辈来指教,该怎么处理

以前看树哥弄过,忘了,望前辈来指教
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 

树哥上面我已经理解了, 
呵呵,我就是想弄成我楼上的那种效果,以前好像看到树哥做个这样的,可忘了是什么时候 
就是想给这个表查询时增加一列排序的,结果就是我楼上的那样 


value1 value2 COL3  
----------- ----------- 
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 

就实现COL3这种排序,


[解决办法]

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 value3 intdeclare @i int,@j intupdate tb set value3=@j,@j=case when @i <> value1 then 1 else isnull(@j,0)+1 end,@i=value1select * from tbalter table tb drop column value3/*value1      value2      value3----------- ----------- -----------1           12          11           13          21           23          30           14          10           15          21           16          10           23          10           22          21           21          11           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 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 select id=identity(int,1,1),* into # from tbselect value1,value2,col3=(select count(1) from # where [value1]=a.[value1] and id<=a.id and id>=(select isnull(max(id),0) from # where id<a.id and value1!=a.value1)) from # a--结果：value1      value2      col3----------- ----------- -----------1           12          11           13          21           23          30           14          10           15          21           16          10           23          10           22          21           21          11           12          2