SQL 重复记录查询的几种方法 - 简书

> 本文由 [简悦 SimpRead](http://ksria.com/simpread/) 转码， 原文地址 [www.jianshu.com](https://www.jianshu.com/p/1035a974435f)

参考文章：  
[http://database.51cto.com/art/201011/235159.htm](http://database.51cto.com/art/201011/235159.htm)

1，查找表中多余的重复记录，重复记录是根据单个字段来判断

```
select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count
(peopleId) > 1)
```

2，删除表中多余的重复记录，重复记录是根据单个字段来判断，只留有 rowid 最小的记录

```
delete from people 
where peopleId in (select   peopleId from people group by   peopleId   having count
(peopleId) > 1)
and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId
)>1)
```

3，查找表中对于的重复记录（多个字段）

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

4，删除表中多余的重复记录（多个字段），只保留 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)
```