Oracle中如何查询重复数据？

在 Oracle 数据库管理中，识别和处理重复数据是一项重要任务，它关系到数据准确性、查询性能以及业务逻辑的一致性，本文将系统介绍在 Oracle 中查找重复数据的多种方法，涵盖核心 SQL 语句、高级技巧及注意事项，帮助读者高效解决实际问题。

理解“重复数据”的定义

在 Oracle 中，“重复数据”通常指主键或唯一约束字段值相同的记录（如身份证号、订单编号等），或非唯一字段组合重复的记录（如姓名+电话号码），需先明确业务场景下的重复判定标准，再选择合适的查询策略。

基础查询方法：GROUP BY + HAVING

对于单列或多列组合的重复检测,GROUP BY 结合 HAVING 子句是最常用的方式，通过统计分组后的记录数，筛选出数量大于 1 的组。

示例 1：单列重复检测

假设表 employees 有 email 列（理论上应唯一），查询重复邮箱：

SELECT email, COUNT(*) AS duplicate_count 
FROM employees 
GROUP BY email 
HAVING COUNT(*) > 1;

示例 2：多列组合重复检测

若需检查 first_name 和 last_name 的组合重复：

SELECT first_name, last_name, COUNT(*) AS duplicate_count 
FROM employees 
GROUP BY first_name, last_name 
HAVING COUNT(*) > 1;

进阶技巧：ROW_NUMBER() 分析函数

当需要定位具体哪些行重复时,可使用窗口函数 ROW_NUMBER() 为每组记录分配序号，过滤出序号大于 1 的行。

示例：定位所有重复记录

WITH duplicate_cte AS (
  SELECT 
    id, 
    email,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn 
  FROM employees
)
SELECT * 
FROM duplicate_cte 
WHERE rn > 1;

此方法能精准返回所有重复行的详细信息,适用于需进一步处理（如删除、更新）的场景。

利用 DISTINCT 与子查询

若仅需获取不重复的唯一值列表,可通过 DISTINCT 或子查询对比全量数据与去重后数据。

方法 1：DISTINCT 对比法

-- 全量表
SELECT COUNT(*) AS total_rows FROM employees;
-- 去重后表
SELECT COUNT(DISTINCT email) AS unique_emails FROM employees;

通过两者差值可快速判断重复数量。

方法 2：NOT IN 子查询

SELECT * 
FROM employees 
WHERE email NOT IN (
  SELECT DISTINCT email FROM employees
);

此方法效率较低,仅适用于小数据集。

性能优化与注意事项

1. 索引优化：对频繁查询的列建立索引（如 CREATE INDEX idx_email ON employees(email)），提升 GROUP BY 性能。
2. 避免全表扫描：大数据集下优先使用分析函数，减少临时表生成。
3. NULL 值处理：Oracle 中 NULL != NULL，需额外处理（如用 COALESCE 转换为默认值）。

实际应用案例

某电商平台的 orders 表需排查重复订单号：

-- 查询重复订单
SELECT order_id, COUNT(*) 
FROM orders 
GROUP BY order_id 
HAVING COUNT(*) > 1;
-- 定位重复订单详情
WITH dup_orders AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY create_time) AS rn 
  FROM orders
)
SELECT * FROM dup_orders WHERE rn > 1;

后续可通过 DELETE 语句移除冗余记录，确保数据一致性。

相关问答 FAQs

Q1：为什么我的查询结果包含 NULL 值？
A：Oracle 中 NULL 参与聚合时会自动忽略，若需统计 NULL 重复，可改用 COUNT(*) 并结合 IS NULL 条件：

SELECT col, COUNT(*) 
FROM table 
GROUP BY col 
HAVING COUNT(*) > 1 OR col IS NULL;

Q2：如何删除重复数据只保留最新一条？
A：利用 ROWID 或时间戳排序删除：

DELETE FROM employees 
WHERE ROWID NOT IN (
  SELECT MAX(ROWID) 
  FROM employees 
  GROUP BY email
);

此方法保留每组最大 ROWID（通常对应最新插入行）。