有关数据排序的问！
大家好！我有一个数据库，里边有十万条数据，数据间有间隔（也就是缺号），而且还有重复的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
