SQL重复记录查询和删除方法-爱红旗渠

MYSQL 查询和删除重复记录的方法很多，下面就为您介绍几种最常用的 MYSQL 查询和删除重复记录的方法，希望对您查询和删除重复数据方面能有所帮助。

SQL重复记录查询的几种方法：

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

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

2. 删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有 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)

5. 查找表中多余的重复记录（多个字段），不包含 rowid 最小的记录

select * 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)

以上 SQL 记录重复数据查询和删除方法，希望能够帮助你。

本文地址 http://94275.cn/2016/11/24/repeat-sql-data/ 作者为 Zhenguo