从mysql数据库删除重复记录只保留其中一条(保留id最小的一条)

最新推荐文章于 2024-05-16 09:35:14 发布

小Hier

最新推荐文章于 2024-05-16 09:35:14 发布

阅读量1.3k

点赞数 1

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

本文链接：https://blog.csdn.net/weixin_41965172/article/details/117997074

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

准备数据表

1.查出重复的series_id

SELECT series_id FROM cmj_used_car_series GROUP BY series_id HAVING count(series_id) > 1;

2.查出重复的series_id数据中最小的id

SELECT min(id) FROM cmj_used_car_series GROUP BY series_id HAVING count(series_id) > 1;

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

SELECT id FROM cmj_used_car_series WHERE series_id IN ( SELECT series_id FROM cmj_used_car_series GROUP BY series_id HAVING count(series_id) > 1) AND id NOT IN (SELECT min(id) FROM cmj_used_car_series GROUP BY series_id HAVING count(series_id) > 1);

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

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

5.删除series_id重复的数据(只保留一条，保留最小id的)

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