有关数据排序的问！



大家好！我有一个数据库，里边有十万条数据，数据间有间隔（也就是缺号），而且还有重复的ID。请问大家怎么才可以消除重号，并且完全重新按数序排序。谢谢了~

------解决方案--------------------
借用一临时表.

select px = identity(int,1,1) , * into tmp from tb order by id 
select * from tmp

------解决方案--------------------
select id from table where count(id)>2 group by id 可以得到重复的ID，然后你可以把修改其中一个ID解决重复

------解决方案--------------------
create table a(id int ,name varchar(10))
insert into a select 1,'a'
insert into a select 1,'a'
insert into a select 1,'c'
insert into a select 2,'c'
insert into a select 3,'c'
insert into a select 3,'c'

select i=identity(int,1,1),* into b from a

--用游标去处理
declare @i int,@id int
declare roy cursor for select i,id from b order by i
open roy

fetch next from roy into @i,@id
while @@fetch_status=0
begin
if (select count(1) from b where b.id=@id )>=2
begin
update b set b.id=(select max(id)+1 from b)
where b.i=@i
end

fetch next from roy into @i,@id
end
close roy
deallocate roy

select * from b
order by id

/*
i id name
----------- ----------- ----------
3 1 c
4 2 c
6 3 c
1 4 a
2 5 a
5 6 c

(6 行受影响)

*/

------解决方案--------------------
用语句更新:
declare @i int
set @i=0
update ta
set col=@i,@i=@i+1