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

最新推荐文章于 2021-02-03 22:04:51 发布

dta0502

最新推荐文章于 2021-02-03 22:04:51 发布

阅读量134

点赞数

分类专栏：数据库文章标签： 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
)