数据库常用操作之删除重复数据且仅保留一条

最新推荐文章于 2025-07-02 00:06:23 发布

原创最新推荐文章于 2025-07-02 00:06:23 发布 · 5.3k 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

Relational Database 同时被 2 个专栏收录

28 篇文章

订阅专栏

Work Development

28 篇文章

订阅专栏

本文介绍如何在MySQL和Oracle数据库中查询并删除重复数据的方法。通过多种SQL脚本示例，包括单字段和多字段操作，帮助读者掌握不同场景下处理重复数据的技术。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

单字段操作

查询全部重复的数据

MySQL实现

sql脚本


    Select * From 表 Where 重复字段 In (Select 重复字段 From 表 Group By 重复字段 Having Count(*)>1)

Oracle实现

查询表中重复数据(id最小除外)

MySQL实现

sql脚本


    -- 方式一
    SELECT 
        *
    FROM
        表
    WHERE
        主键 NOT IN (
            SELECT
                t.主键别名
            FROM
                (
                    SELECT
                        MIN(主键) AS 主键别名
                    FROM
                        表
                    GROUP BY
                        重复字段
                ) t
        )


    -- 方式二
    SELECT * FROM 表 AS ta WHERE ta.唯一键 <> ( SELECT min( tb.唯一键 ) FROM 表 AS tb WHERE ta.判断重复的列 = tb.判断重复的列 );


    -- 方式三(使用exists 替代 in)
    SELECT 
        *
    FROM
        表 t1
    WHERE
        NOT EXISTS (
            SELECT
                1
            FROM
                (
                    SELECT
                        MIN(主键) AS 主键别名
                    FROM
                        表
                    GROUP BY
                        重复字段
                ) t2
            WHERE 
                t2.主键别名 = t1.主键
        )

执行结果分析

方式二查询的数据会比方式一少一些，因为重复的列可能存在为空的情况，导致关联失败，无法查询，mysql可以使用ifnull()函数将对应重复字段替换成指定字符，如0，Oracle使用nvl()函数。
```
    SELECT * FROM 表 AS ta WHERE ta.唯一键 <> ( SELECT min( tb.唯一键 ) FROM 表 AS tb WHERE ifnull(ta.判断重复的列,'0') = ifnull(tb.判断重复的列,'0') );
```
在判断null==null时结果为false；故查询数据为空。Oracle中在处理千万数据之下时，可以使用内部函数nvl（null,‘0’）赋相同值判断两个空值。如果是千万以上数据建议使用isnull判断。目的是消除nvl对索引的影响。

删除表中多余重复数据并且只留1条

MySQL实现

方式一


    delete from 表 where 主键 not in (select min(主键) as 主键别名 from 表 group by 重复字段);

注意事项

方式一执行会出现如下错误：[Err] 1093 - You can’t specify target table ‘dept’ for update in FROM clause

原因是：更新这个表的同时又查询了这个表，查询这个表的同时又去更新了这个表，可以理解为死锁。mysql不支持这种更新查询同一张表的操作

解决办法：把要更新的几列数据查询出来做为一个第三方表，然后筛选更新

方式二(推荐使用)



    delete from 表 where 主键 not in (select 表别名.主键别名 from (select min(主键) as 主键别名 from 表 group by 重复字段) 表别名) ;

    -- 删除username相同的重复数据，保留id最小的用户信息
    delete from 
        student 
    where
        username in (select username from ( select username from student group by username having count(username)>1) a)
    and 
        id not in ( select id from (select min(id) as id from student group by username having count(username)>1 ) b)

    -- 简写
    delete from student where id not in (select id from (select min(id) as id from student group by username) b);

方式三


    DELETE 
    FROM
        table_name AS ta 
    WHERE
        ta.唯一键 <> (
    SELECT
        t.maxid 
    FROM
        ( SELECT max( tb.唯一键 ) AS maxid FROM table_name AS tb WHERE ta.判断重复的列 = tb.判断重复的列 ) t 
        );

Oracle实现

多字段操作

删除表中多余重复数据并且只留1条

方式一


    DELETE
    FROM
        dept
    WHERE
        (dname, db_source) IN (
            SELECT
                t.dname,
                t.db_source
            FROM
                (
                    SELECT
                        dname,
                        db_source
                    FROM
                        dept
                    GROUP BY
                        dname,
                        db_source
                    HAVING
                        count(1) > 1
                ) t
        )
    AND deptno NOT IN (
        SELECT
            dt.mindeptno
        FROM
            (
                SELECT
                    min(deptno) AS mindeptno
                FROM
                    dept
                GROUP BY
                    dname,
                    db_source
                HAVING
                    count(1) > 1
            ) dt
    )

其他

count(*)与count(1)与count(列名)异同

count(*)与count(1)其实没有什么差别，用哪个都可以

count(*)与count(列名)的区别

count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入)
in 和 exists关键字

小表驱动大表用IN，大表驱动小表用EXISTS。IN适合的情况是外表数据量小的情况，而不是外表数据大的情况，因为IN会遍历外表的全部数据，假设a表100条，b表10000条那么遍历次数就是100*10000次，而exists则是执行100次去判断a表中的数据是否在b表中存在，它只执行了a.length次数。至于哪一个效率高是要看情况的，因为in是在内存中比较的，而exists则是进行数据库查询操作的。

参考链接

mysql删除重复记录并且只保留一条

https://blog.csdn.net/n950814abc/article/details/82284838
SQL IN与EXISTS的执行效率比较

https://www.cnblogs.com/AllUserBegin/p/3513084.html
sql优化–in和exists效率

https://www.cnblogs.com/weifeng123/p/9530758.html