create table #t(id int identity(1,1),code int,name varchar(10)) insert #t select 7,'aa1' union all select 9,'aa2' union all select 2,'aa3' union all select 16,'aa4' union all select 8,'aa5' union all select 5,'aa6' union all select 12,'aa7' union all select 13,'aa8' union all select 14,'aa9'declare @n int,@id0 int,@code0 int,@name0 varchar(10),@id int,@code int,@name varchar(10)select @id0=0,@n=max(id) from #t while @n<>0 begin declare c_t cursor for select [id],code,[name] from #t where id>=@id0-1 open c_t fetch next from c_t into @id0,@code0,@name0 while @@fetch_status=0 begin fetch next from c_t into @id,@code,@name if @@fetch_status=0 begin if @code0>@code begin update #t set code=@code,name=@name where id=@id0 update #t set code=@code0,name=@name0 where id=@id set @n=@id0 goto nextcode end select @id0=case when @code0>@code then @id0 else @id end,@code0=@code,@name0=@name--,@n=@id end end set @n=@n-1 nextcode: close c_t deallocate c_t end select * from #t
insert #t
select 7,'aa1' union all
select 9,'aa2' union all
select 2,'aa3' union all
select 16,'aa4' union all
select 8,'aa5' union all
select 5,'aa6' union all
select 12,'aa7' union all
select 13,'aa8' union all
select 14,'aa9'declare @n int,@id0 int,@code0 int,@name0 varchar(10),@id int,@code int,@name varchar(10)select @id0=0,@n=max(id) from #t
while @n<>0
begin
declare c_t cursor for select [id],code,[name] from #t where id>=@id0-1
open c_t
fetch next from c_t into @id0,@code0,@name0 while @@fetch_status=0
begin
fetch next from c_t into @id,@code,@name
if @@fetch_status=0
begin
if @code0>@code
begin
update #t set code=@code,name=@name where id=@id0
update #t set code=@code0,name=@name0 where id=@id
set @n=@id0
goto nextcode
end
select @id0=case when @code0>@code then @id0 else @id end,@code0=@code,@name0=@name--,@n=@id
end
end
set @n=@n-1
nextcode:
close c_t
deallocate c_t
end
select * from #t