Oracle 如何查找并删除重复记录

概述本文介绍如何在 Oracle 数据库中查找并删除重复记录，包括使用聚合函数加上子查询或者连接查询、使用分析函数等方法查找单个字段或者多个字段中的重复数据，以及使用子查询中分析函数等方法实现重复数据的删除。

文章目录 创建示例表使用聚合函数查找重复记录基于单个字段的重复记录基于多个字段的重复记录使用分析函数查找重复记录基于单个字段的重复记录基于多个字段的重复记录删除重复记录使用子查询删除重复记录使用分析函数删除重复记录总结

大家好，我是只谈技术不剪发的 Tony 老师。由于一些历史原因或者人为的误 *** 作，可能会导致数据表中存在重复的记录。前文介绍了如何查找 MysqL 中的重复数据以及如何删除这些重复的记录，今天我们继续讨论如何在 Oracle 数据库中实现相同的功能。

创建示例表

我们首先创建一个示例表 people 并生成一些数据：

CREATE table people (    ID integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    name varchar2(50) NOT NulL,    email varchar2(100) NOT NulL);INSERT INTO people(name, email)VALUES ('张三', 'zhangsan@test.com');INSERT INTO people(name, email)VALUES ('李四', 'lisi@test.com');INSERT INTO people(name, email)VALUES ('王五', 'wangwu@test.com');INSERT INTO people(name, email)VALUES ('李斯', 'wangwu@test.com');SELECT * FROM people;ID|name |EMAIL            |--|-----|-----------------| 1|张三  |zhangsan@test.com| 2|李四  |lisi@test.com    | 3|王五  |wangwu@test.com  | 4|李斯  |lisi@test.com    | 5|王五  |wangwu@test.com  | 6|王五  |wangwu@test.com  |

其中，2 和 4 的 email 字段存在重复数据；3、5 和 6 的 name 和 email 字段存在重复数据。

此时，如果我们想要为 email 字段创建一个唯一约束，将会返回错误：

ALTER table people ADD CONSTRAINT uk_people_email UNIQUE (email);ERROR at line 1:ORA-02299: cannot valIDate (TONY.UK_PEOPLE_EMAIL) - duplicate keys found

显然，我们必须找出并删除 email 字段中的重复记录才能创建唯一约束。

使用聚合函数查找重复记录 基于单个字段的重复记录

如果想要找出 email 重复的数据，可以基于该字段进行分组统计，并且返回行数大于 1 的分组：

SELECT email, count(email)FROM peopleGROUP BY emailHAVING count(email) > 1;EMAIL          |COUNT(EMAIL)|---------------|------------|lisi@test.com  |           2|wangwu@test.com|           3|

查询结果显示有两个邮箱地址存在重复情况。如果想要查看完整的重复数据，可以使用子查询或者连接查询：

SELECT *FROM peopleWHERE email IN (      SELECT email      FROM people      GROUP BY email      HAVING count(email) > 1)ORDER BY email;ID|name |EMAIL          |--|-----|---------------| 2|李四  |lisi@test.com  | 4|李斯  |lisi@test.com  | 3|王五  |wangwu@test.com| 5|王五  |wangwu@test.com| 6|王五  |wangwu@test.com|WITH d AS (  SELECT email  FROM people  GROUP BY email  HAVING count(email) > 1)SELECT p.*FROM people pJOIN d ON (d.email = p.email)ORDER BY p.email;ID|name |EMAIL          |--|-----|---------------| 2|李四  |lisi@test.com  | 4|李斯  |lisi@test.com  | 3|王五  |wangwu@test.com| 5|王五  |wangwu@test.com| 6|王五  |wangwu@test.com|

另一种查找重复记录的方法就是直接使用自连接查询和 distinct *** 作符，例如：

SELECT disTINCT p.*FROM people pJOIN people d ON p.email = d.emailWHERE p.ID <> d.IDORDER BY p.email;ID|name |EMAIL          |--|-----|---------------| 2|李四  |lisi@test.com  | 4|李斯  |lisi@test.com  | 3|王五  |wangwu@test.com| 5|王五  |wangwu@test.com| 6|王五  |wangwu@test.com|

注意不能省略 distinct，否则会某些数据（3、5、6）会返回多次。

基于多个字段的重复记录

如果我们想要找出 name 和 email 两个字段都重复的数据，可以基于这两个字段进行分组统计：

SELECT *FROM peopleWHERE (name, email) IN (      SELECT name, email      FROM people      GROUP BY name, email      HAVING count(1) > 1)ORDER BY email;ID|name |EMAIL          |--|-----|---------------| 3|王五  |wangwu@test.com| 5|王五  |wangwu@test.com| 6|王五  |wangwu@test.com|WITH d AS (  SELECT name, email  FROM people  GROUP BY name, email  HAVING count(*) > 1)SELECT p.*FROM people pJOIN d ON (d.name = p.name AND d.email = p.email)ORDER BY p.email;ID|name |EMAIL          |--|-----|---------------| 3|王五  |wangwu@test.com| 5|王五  |wangwu@test.com| 6|王五  |wangwu@test.com|

只有当 name 和 email 都相同时才是重复数据，所以 2 和 4 不是重复记录。

使用分析函数查找重复记录

使用聚合函数查找重复记录需要扫描同一个表两次，如果表中的数据量很大时，可能存在性能问题。为此，我们可以采用另一种方法：分析函数（窗口函数）。

基于单个字段的重复记录

首先，我们通过 count() 分析函数找出每个 email 出现的次数：

SELECT ID, name, email, count(*) over (partition by email) cntFROM people;ID|name |EMAIL            |CNT|--|-----|-----------------|---| 4|李斯  |lisi@test.com    |  2| 2|李四  |lisi@test.com    |  2| 6|王五  |wangwu@test.com  |  3| 3|王五  |wangwu@test.com  |  3| 5|王五  |wangwu@test.com  |  3| 1|张三  |zhangsan@test.com|  1|

分析函数不仅可以找出字段的重复次数，同时还可以保留原表中的数据，避免了二次扫描的 *** 作。接下来我们只需要返回次数大于 1 的记录即可：

WITH d AS (  SELECT ID,          count(*) over (partition by email) cnt  FROM people)SELECT *FROM dWHERE cnt > 1ORDER BY ID;ID|name |EMAIL          |CNT|--|-----|---------------|---| 2|李四  |lisi@test.com  |  2| 3|王五  |wangwu@test.com|  3| 4|李斯  |lisi@test.com  |  2| 5|王五  |wangwu@test.com|  3| 6|王五  |wangwu@test.com|  3|

基于多个字段的重复记录

分析函数同样支持基于多个字段的分区 *** 作，以下语句可以用于找出 name 和 email 两个字段都重复的数据：

WITH d AS (  SELECT ID,          count(*) over (partition by name, email) cnt  FROM people)SELECT *FROM dWHERE cnt > 1ORDER BY ID;ID|name |EMAIL          |CNT|--|-----|---------------|---| 3|王五  |wangwu@test.com|  3| 5|王五  |wangwu@test.com|  3| 6|王五  |wangwu@test.com|  3|

显然，分析函数不但拥有更好的性能，也具有更好的可读性。

删除重复记录

记下来需要解决的问题就是如何删除这些重复记录，通常我们需要保留其中的一条记录。

使用子查询删除重复记录

假如我们想要删除 email 字段重复的记录，只保留其中 ID 最大的一条；可以使用子查询找出需要保留的数据，然后删除其他的数据：

DELETE FROM peopleWHERE ID NOT IN (      SELECT max(ID)      FROM people      GROUP BY email     );

删除之后再次查询 people 表：

SELECT * FROM people;ID|name |EMAIL            |--|-----|-----------------| 1|张三  |zhangsan@test.com| 4|李斯  |lisi@test.com    | 6|王五  |wangwu@test.com  |

也可以使用关联子查询删除重复的数据：

DELETE FROM people pWHERE p.ID NOT IN (      SELECT max(ID)      FROM people      WHERE email = p.email     );

在执行上面的语句之前，记得重新创建 people 表并生成测试数据。

总结

以上是内存溢出为你收集整理的Oracle 如何查找并删除重复记录全部内容，希望文章能够帮你解决Oracle 如何查找并删除重复记录所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/1153871.html