大家好！我有一个数据库，里边有十万条数据，数据间有间隔（也就是缺号），而且还有重复的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 [email protected] )>=2
begin
update b set b.id=(select max(id)+1 from b)
where [email protected]
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 [email protected],@[email protected]+1