首先，如何查询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
（所影响的行数为 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
--测试结果 /*----------------------------- select * from tt -----------------------------*/ id pid ----------- ----------- 1 1 2 2 3 3
