MySQL中删除重复数据只保留一条

dta0502

于 2019-03-05 21:37:34 发布

阅读量143

点赞数

分类专栏：数据库文章标签： MySQL

数据库专栏收录该内容

42 篇文章

订阅专栏

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

SELECT *
FROM people
WHERE peopleName IN (
        SELECT peopleName
        FROM people
        GROUP BY peopleName
        HAVING count(peopleName) > 1
    )

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

DELETE
FROM people
WHERE peopleName IN (
        SELECT peopleName
        FROM people
        GROUP BY peopleName
        HAVING count(peopleName) > 1
    )
AND peopleId NOT IN (
    SELECT min(peopleId)
    FROM people
    GROUP BY peopleName
    HAVING count(peopleName) > 1
)