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

原创已于 2023-09-25 10:54:14 修改 · 3.3k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

于 2018-08-15 10:44:48 首次发布

MySql 专栏收录该内容

25 篇文章

订阅专栏

本文介绍如何使用SQL语句从数据库表中删除重复记录，并保留指定条件下的唯一记录。包括单字段重复记录删除及多字段重复记录删除的方法。

#删除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
);