前文介绍了如何查找 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
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,否则会某些数据(3、5、6)会返回多次。
基于多个字段的重复记录
如果我们想要找出 name 和 email 两个字段都重复的数据,可以基于这两个字段进行分组统计:
SELECT *
FROM people
WHERE (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 p
JOIN 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) cnt
FROM 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, name, email,
count(*) over (partition by email) cnt
FROM people)
SELECT *
FROM d
WHERE cnt > 1
ORDER 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, name, email,
count(*) over (partition by name, email) cnt
FROM people)
SELECT *
FROM d
WHERE cnt > 1
ORDER 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 people
WHERE 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 p
WHERE p.id NOT IN (
SELECT max(id)
FROM people
WHERE email = p.email
);
在执行上面的语句之前,记得重新创建 people 表并生成测试数据。
💡想一想,如果想要保留重复数据中 id 最小的数据应该怎么实现呢?
使用分析函数删除重复记录
ROW_NUMBER() 分析函数可以用于将数据进行分组,然后为每一条数据分配一个唯一的数字编号。例如:
SELECT id, name, email,
row_number() over (PARTITION BY email ORDER BY id) AS row_num
FROM people;
ID|NAME|EMAIL |ROW_NUM|
--|----|------------------|-------|
2|李四 |lisi@test.com | 1|
4|李斯 |lisi@test.com | 2|
3|王五 |wangwu@test.com | 1|
5|王五 |wangwu@test.com | 2|
6|王五 |wangwu@test.com | 3|
1|张三 |zhangsan@test.com| 1|
以上语句基于 email 分组(PARTITION BY email),同时按照 id 进行排序(ORDER BY id),然后为每个组内的数据分配一个编号;如果编号大于 1 就意味着存在重复的数据。
📝除了 ROW_NUMBER() 之外,RANK() 或者 DENSE_RANK() 函数也可以实现以上功能。关于窗口函数的介绍和使用案例,可以参考这篇文章。
我们可以基于该查询结果删除重复的记录:
DELETE
FROM people
WHERE id IN (
SELECT id
FROM (
SELECT id, name, email,
row_number() over (PARTITION BY email ORDER BY id DESC) AS row_num
FROM people) d
WHERE row_num > 1);
在执行上面的语句之前,记得重新创建 people 表并生成测试数据。
基于多个字段的重复数据删除方法和单个字段类似,大家可以自行尝试。最后,为了防止再次产生重复数据,可以增加一个唯一约束。例如:
ALTER TABLE people ADD CONSTRAINT uk_people_email UNIQUE (email);
总结
本文介绍了如何在 Oracle 数据库中查找并删除重复记录,包括使用聚合函数加上子查询或者连接查询、使用分析函数等方法查找单个字段或者多个字段中的重复数据,以及使用子查询中分析函数等方法实现重复数据的删除。