Sql如何删除重复记录

if OBJECT_ID('tempdb..#tmp') is not null
drop table #tmp 
GO 
select distinct * into #tmp 
from duplicate_all 
where c1 = 1 
GO 
delete duplicate_all where c1 = 1 
GO 
insert into duplicate_all 
select * from #tmp

		

with tmp 
as
( 
select *,ROW_NUMBER() OVER(PARTITION BY c1,c2,c3 ORDER BY(getdate())) as num 
from duplicate_all 
where c1 = 1 
) 
delete tmp where num > 1

		

if OBJECT_ID('tmp') is not null
drop table tmp 
GO 
create table tmp 
( 
c1 int, 
c2 int, 
c3 varchar(100), 
constraint UQ_01 unique(c2,c3) with(IGNORE_DUP_KEY = ON) 
) 
GO 
insert into tmp 
select * from duplicate_col 
select * from tmp

		

delete from duplicate_col 
where exists(select 1 from duplicate_col b where duplicate_col.c1 > b.c1 and (duplicate_col.c2 = b.c2 and duplicate_col.c3 = b.c3)) 

--或者

delete from duplicate_col 
where c1 not in (select min(c1) from duplicate_col group by c2,c3)
		

with tmp 
as
( 
select *,ROW_NUMBER() OVER(PARTITION BY c2,c3 ORDER BY(getdate())) as num 
from duplicate_col 
) 
delete tmp where num > 1 
select * from duplicate_col