create table #tb(id int,newid int)
insert into #tb(id,newid) values(1,1)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(1,1)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(3,3)
insert into #tb(id,newid) values(3,3)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid) values(2,2)
insert into #tb(id,newid)
select distinct id+1000,newid+1000 from #tb
方法大体有:
1.游标。
上面有朋友写过了,我写的话也是差不多的,只是细节上略有差别,就不写了。
2.临时表或表变量
declare @t table(id int,newid int)
insert @t select distinct id,newid from tb
truncate table tb
insert tb select id,newid from @t
3,加标识列
create table tb(id int,newid int)
insert into tb(id,newid) values(1,1)
insert into tb(id,newid) values(2,2)
insert into tb(id,newid) values(2,2)
insert into tb(id,newid) values(1,1)
insert into tb(id,newid) values(2,2)
insert into tb(id,newid) values(3,3)
insert into tb(id,newid) values(3,3)
insert into tb(id,newid) values(2,2)
insert into tb(id,newid) values(2,2)
alter table tb add idx int identity(1,1)
go
delete a from tb a where exists(select 1 from tb where idx<a.idx and checksum(a.id,a.newid)=checksum(id,newid))
go
alter table tb drop column idx
select * from tb
go
drop table tb
go
declare @counts int
declare @id int
declare record cursor for select id,count(*) from Testtable group by id having count(*) > 1
open record
fetch record into @id,@counts
while @@fetch_status=0
begin
select @counts = @counts -1
set rowcount @counts
delete from Testtable where id = @id
fetch record into @id,@counts
end
close record
deallocate record
set rowcount 0
select * from Testtable order by id
declare @counts int
declare @id int
declare record cursor for select id,count(*) from Testtable group by id having count(*) > 1
open record
fetch record into @id,@counts
while @@fetch_status=0
begin
select @counts = @counts -1
set rowcount @counts
delete from Testtable where id = @id
fetch record into @id,@counts
end
close record
deallocate record
set rowcount 0
select * from Testtable order by id