sql 查询，删除重复的记录

一、            查找重复记录

1.查找全部重复记录

Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(重复字段)>1)

 

2.过滤重复记录(只显示一条)

有ID的情况下，将表中重复的记录只显示一条，不重复的记录也显示出来

Select * From 表 Where ID In (Select Max(ID) From 表 Group By 重复字段)

注：此处显示ID最大一条记录

 

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

select * from vitae a

where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

二、            删除重复记录

1.删除全部重复记录（慎用）

Delete 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(重复字段)>1)

 

2.保留一条（这个应该是大多数人所需要的 ^_^）

Delete 表 Where ID Not In (Select Max(ID) From HZT Group By 重复字段)

注：此处保留ID最大一条记录

 

3.删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from vitae a

where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)