在维护数据质量的过程中，无论是清理客户名单、交易日志还是其他数据集，删除重复行都是至关重要的。本指南将探讨在SQL数据库中删除重复行的实用技术，包括详细的语法和真实世界的例子，以帮助高效地处理和消除这些重复数据。
识别SQL数据库中重复记录的常见原因。探索各种方法来定位和删除重复条目。理解SQL语法和实际方法来删除重复项。学习在清理重复数据时确保数据完整性的最佳实践。
在SQL中删除重复行可以通过几种方法实现。每种方法根据使用的数据库系统和任务的具体需求都有其自身的优势。以下是一些有效的技术来删除重复记录。
重复行可能由于以下原因出现在数据库中：
在删除重复项之前，需要定位它们。当多个行在一个或多个列中包含相同的值时，通常会出现重复项。以下是如何识别这些重复项的方法：
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
假设有一个名为employees的表，包含以下数据：
| id | name | |
|---|---|---|
| 1 | Alice | [email protected] |
| 2 | Bob | [email protected] |
| 3 | Carol | [email protected] |
| 4 | Alice | [email protected] |
| 5 | Dave | [email protected] |
要找到重复的电子邮件：
SELECT email, COUNT(*)
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;
输出结果将显示出现超过一次的电子邮件。
一种强大的删除重复项的方法涉及ROW_NUMBER()窗口函数，它为每个分区内的每行分配一个唯一的顺序号。
WITH CTE AS (
SELECT column1, column2,
ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT NULL)) AS rn
FROM table_name
)
DELETE FROM CTE
WHERE rn > 1;
例如，要从employees表中基于email删除重复行：
WITH CTE AS (
SELECT id, name, email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM employees
)
DELETE FROM CTE
WHERE rn > 1;
执行上述查询后，表将被清理，结果如下：
| id | name | |
|---|---|---|
| 1 | Alice | [email protected] |
| 2 | Bob | [email protected] |
| 3 | Carol | [email protected] |
| 5 | Dave | [email protected] |
重复的行（id = 4）已被删除。
另一种有效的策略涉及使用自连接来检测和删除重复行。
DELETE t1
FROM table_name t1
JOIN table_name t2
ON t1.column1 = t2.column1
AND t1.column2 = t2.column2
AND t1.id < t2.id;
例如，要从employees表中删除重复条目：
DELETE e1
FROM employees e1
JOIN employees e2
ON e1.email = e2.email
AND e1.id < e2.id;
执行此查询后，表将如下所示：
| id | name | |
|---|---|---|
| 1 | Alice | [email protected] |
| 2 | Bob | [email protected] |
| 3 | Carol | [email protected] |
| 5 | Dave | [email protected] |
id = 4的行已被删除，只留下唯一的条目。
有时，创建一个包含唯一记录的新表并替换旧表是最安全的方法。
CREATE TABLE new_table AS
SELECT DISTINCT *
FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;
例如，要清理employees表中的重复项：
CREATE TABLE employees_unique AS
SELECT DISTINCT *
FROM employees;
DROP TABLE employees;
ALTER TABLE employees_unique RENAME TO employees;
新的employees表现在将如下所示：
| id | name | |
|---|---|---|
| 1 | Alice | [email protected] |
| 2 | Bob | [email protected] |
| 3 | Carol | [email protected] |
| 5 | Dave | [email protected] |
employees表现在没有重复项。