查出重复的type

SELECT type FROM table GROUP BY type HAVING count(type) > 1;

查出重复的type数据中最小的id

SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1;

查出重复的type数据中非最小的id(需要删除的)

SELECT id FROM table WHERE type in(
    SELECT type FROM table GROUP BY type HAVING count(type) > 1)
    AND id  not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1);

在Mysql中是不能删除查询出来的记录,而是要通过一张临时表来解决

SELECT id from (
    SELECT id FROM table WHERE type in(
        SELECT type FROM table GROUP BY type HAVING count(type) > 1)
        AND id  not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1)
) as t;

删除type重复的数据(只保留一条,保留最小id的)

DELETE FROM table WHERE id IN (
    SELECT id from (
        SELECT id FROM table WHERE type in(
            SELECT type FROM table GROUP BY type HAVING count(type) > 1)
            AND id  not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1)
    ) as t
);
Last modification:November 18, 2020

本文标题:MySQL数据库删除重复记录只保留其中一条

本文链接:https://blog.gumengya.com/63.html

除非另有说明,本作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议

声明:转载请注明文章来源。

如果觉得我的文章对你有用,请随意赞赏
双击文章内容区域可以给本文点赞哦,快来试试吧