生产实例:删除一个表中的重复数据,只保留id最小的mysql

关注

原创 2023-09-25 10:54:14 3.4k 阅读 AI 写同款· GEO 优化 ›

Excel中去重并只保留最近n次日期/最大最小值的数据 Excel中去重并只保留最近n次日期/最大最小值的数据产品ID 日期记录 1 2020/6/1 已投产 2 2020/6/1 停产 4 2020/5/1 产出23件 1 2020/4/6 准备中 2 2020/4/6 下线中 3 2020/3/26 准备中 5 2020/3/26 已投产 1 2020/2/26 准备中 1 2020/1/26 评估中其中我们需要拿到每个产品最新的记录状态，结果预期如下：阅读详情

#删除t_jt_contract_user表中的重复数据,只保留id最小的
DELETE FROM t_jt_contract_user WHERE userIDCard IN (SELECT a.userIDCard 
FROM(SELECT userIDCard FROM t_jt_contract_user GROUP BY userIDCard HAVING 
COUNT(userIDCard) > 1) a) AND id NOT IN (SELECT b.bid FROM(SELECT min(id) AS 
bid FROM t_jt_contract_user GROUP BY userIDCard HAVING COUNT(userIDCard) > 1) 
b);

简洁版的sql:(删除表中多余的重复记录，重复记录是根据单个字段（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)

根据多个重复字段,删除重复记录

DELETE
FROM
    `表名`
WHERE
    (重复字段1,重复字段2) IN (
        SELECT
            a.重复字段1,a.重复字段2
        FROM
            (
                SELECT
                    重复字段1,重复字段2
                FROM
                    `表名`
                GROUP BY
                    重复字段1,重复字段2
                HAVING
                    count(1) > 1
            ) AS a
    )
AND id NOT IN (
    SELECT
        b.aa
    FROM
        (
            SELECT
                min(id) AS aa
            FROM
                `表名`
            GROUP BY
                重复字段1,重复字段2
            HAVING
                count(1) > 1
        ) AS b
);