MSsql删除重复记录

查找表中多余的重复记录（多个字段）

查询

select f1,f2,f3,f4,COUNT(1) c,(COUNT(1)-1) c2 from TableTest where f5='zzz' and f6='tt'
group by f1,f2,f3,f4 having count(*) > 1 order by c desc

需要删除的字段ID记录

select* from TableTest where id
in
( select Id from [TableTest] a join
(
select f1,f2,f3,f4 from [TableTest] where f5='zzz' and f6='tt'
group by f1,f2,f3,f4 having count(*) > 1 ) b on a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3 and a.f4=b.f4 and
f5='zzz' and f6='tt' )
and id not in
( select ID from [TableTest] a join
(
select f1,f2,f3,f4,min(daddtime) as time from [TableTest] where f5='zzz' and f6='tt'
group by f1,f2,f3,f4 having count(*) > 1) b on a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3 and a.f4=b.f4 and a.dAddTime=b.time
and f5='zzz' and f6='tt' )

delete from TableTest where id
in
( select Id from [TableTest] a join
(
select f1,f2,f3,f4 from [TableTest] where f5='zzz' and f6='tt'
group by f1,f2,f3,f4 having count(*) > 1 ) b on a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3 and a.f4=b.f4
and f5='zzz' and f6='tt' )
and id not in
( select ID from [TableTest] a join
(
select f1,f2,f3,f4,min(daddtime) as time from [TableTest] where f5='zzz' and f6='tt'
group by f1,f2,f3,f4 having count(*) > 1) b on a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3 and a.f4=b.f4 and a.dAddTime=b.time
and f5='zzz' and f6='tt' )