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 [email protected],@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 [email protected],
@flag=case when [email protected] 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