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

文章目录

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

由于一些历史原因或者人为的误操作，可能会导致数据表中存在重复的记录。前文介绍了如何查找 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 ('李斯', '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 people
GROUP BY email
HAVING count(email) > 1;
EMAIL          |COUNT(EMAIL)|
---------------|------------|
lisi@test.com  |           2|
wangwu@test.com|           3|



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

SELECT *
FROM people
WHERE 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 p
JOIN 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 p
JOIN people d ON p.email = d.email
WHERE p.id <> d.id

更多请见：http://www.mark-to-win.com/tutorial/51518.html