SQL查询与删除重复记录：MySQL、Oracle等数据库实用方案

准备条件与风险提醒

在执行任何 DELETE 语句前，必须首先明确保留哪一条记录（通常是主键最大或最小的那条）。由于删除操作不可逆，强烈建议先备份相关数据或在测试环境中验证 SQL 的 SELECT 结果，确保筛选条件符合预期后再执行删除。

查询重复记录的通用方法

在编写删除语句之前，第一步往往是准确找出哪些数据是重复的。以下以表 employee 为例，展示如何根据单个或多个字段查找重复项。

第1步：按单字段查找重复记录

当需要根据单一列（如 emp_id）判断重复时，可以使用聚合函数结合 HAVING 子句。该语句返回出现次数大于1的所有分组信息：

SELECT emp_id, COUNT(*)
FROM employee
GROUP BY emp_id
HAVING COUNT(*) > 1;

如果需要查看完整的重复记录行，可以配合子查询使用：

SELECT * FROM employee
WHERE emp_id IN (
    SELECT emp_id FROM employee GROUP BY emp_id HAVING COUNT(*) > 1
);

第2步：按多字段组合查找重复记录

在实际业务中，往往需要多个字段共同构成“重复”的定义（例如姓名和地址同时相同才算重复）。在 MySQL 或 Oracle 中，可以在 GROUP BY 后列出所有判断字段：

SELECT emp_id, emp_name, salary, COUNT(*)
FROM employee
GROUP BY emp_id, emp_name, salary
HAVING COUNT(*) > 1;

删除重复记录的实操方案

根据数据库类型的不同（特别是 MySQL 不支持直接自引用删除 vs Oracle 拥有 ROWID），删除策略有所区别。

第3步：MySQL 环境下删除重复记录

在 MySQL 中，不能直接在 DELETE 的目标表中包含子查询引用的同一张表。推荐以下两种稳健的方法：

方法 A：保留 ID 最大的记录（适用于有自增主键的场景）

思路是：删除那些不在各自分组最大 ID 列表中的记录。

DELETE FROM YourTable
WHERE id NOT IN (
    SELECT max_id FROM (
        SELECT MAX(id) AS max_id 
        FROM YourTable 
        GROUP BY name, value
    ) AS tmp
);

方法 B：使用内部连接（JOIN）删除

通过左连接找到重复组内非保留的记录进行删除，效率通常优于嵌套子查询：

DELETE a FROM YourTable a
LEFT JOIN (
    SELECT MIN(id) AS keep_id FROM YourTable GROUP BY name, value
) b ON a.id = b.keep_id
WHERE b.keep_id IS NULL;

第4步：Oracle 环境下删除重复记录

Oracle 数据库为每行数据提供了唯一的伪列 ROWID，这使得删除操作更加直观且高效，无需担心自引用限制。

方法 C：利用 ROWID 直接删除

保留每组中 ROWID 最大（即最后插入）的那条记录，删除其余的：

DELETE FROM employee e2
WHERE ROWID NOT IN (
    SELECT MAX(e1.ROWID)
    FROM employee e1
    WHERE e1.emp_id = e2.emp_id 
    AND e1.emp_name = e2.emp_name 
    AND e1.salary = e2.salary
);

或者使用更简练的分组写法（效率更高）：

DELETE FROM employee
WHERE ROWID NOT IN (
    SELECT MAX(t1.ROWID)
    FROM employee t1
    GROUP BY t1.emp_id, t1.emp_name, t1.salary
);

第5步：通过临时表重建数据（适用于全量去重）

如果重复率极高，或者需要进行复杂的清洗，可以采用“导出-清空-导入”的方式。这在某些大数据量场景下比逐行删除更快：

1. 创建临时表并插入去重后的数据：
   CREATE TABLE temp_emp AS (SELECT DISTINCT * FROM employee);
2. 清空原表：
   TRUNCATE TABLE employee;
3. 将临时表数据插回：
   INSERT INTO employee SELECT * FROM temp_emp;
4. 删除临时表：
DROP TABLE temp_emp;

完成判断与后续建议

执行完删除操作后，务必再次运行查询步骤中的 SELECT ... GROUP BY ... HAVING COUNT(*) > 1 语句，确认结果集为空，即为删除完成。为了防止未来数据继续重复，建议在数据库设计层面增加 UNIQUE 索引或多字段唯一约束。

以上就是SQL删除重复记录方法的详细内容，更多关于数据库优化的资料请关注本站其它相关文章！