首先，如何查詢table中有重復記錄 select *,count(1) as rownum from tt group by id, pid having count(1) > 1 id pid rownum ----------- ----------- ----------- 1 1 2 3 3 3
（所影響的行數(shù)為 2 行）
方法一:使用distinct和臨時表 if object_id('tempdb..#tmp') is not null drop table #tmp select distinct * into #tmp from tt truncate table tt insert into tt select * from #tmp
方法二:添加標識列 alter table tt add newid int identity(1,1) go delete from tt where exists(select 1 from tt a where a.newid>tt.newid and tt.id=a.id and tt.pid=a.pid) go alter table tt drop column newid go
--測試結(jié)果 /*----------------------------- select * from tt -----------------------------*/ id pid ----------- ----------- 1 1 2 2 3 3
