删除重复行的几种方法

以下是测试数据：

 

if object_id('tb') is not null
    drop table tb
go
create table tb(A int,B int)
insert into tb
select 1,1
union all select 2,2
union all select 3,3
union all select 4,4
union all select 5,5
union all select 1,1
union all select 2,2
union all select 1,1
union all select 2,2
with cte as
(
select row_number() over (partition by A order by A,B) as num, A,B from tb
)
delete from cte where  num > 1
select * from tb order by A,B
/*
A           B
----------- -----------
1           1
2           2
3           3
4           4
5           5

(5 行受影响)
*/

;

 

方法一

方法二

 

 

select distinct * into tmp from tb;
truncate table tb;
insert into tb select * from tmp;

 

 

方法三

alter table tb add  ID int identity(1,1)

delete from tb where
exists( select 1 from tb a where a.A=tb.A and a.B=tb.B and a.ID>tb.ID )
alter table tb drop column id
select * from tb