如何在 SQL 中高效查询重复记录？

SQL 查询重复记录的方法是使用 GROUP BY 和 HAVING 子句。通过 GROUP BY 对某个或多个字段进行分组，然后使用 HAVING 子句筛选出分组后记录数大于1的记录。

在数据库管理中，查找和处理重复记录是一个常见的任务，重复记录是指具有相同数据的多个条目，这可能是由于错误的数据插入、数据传输问题或其他原因导致的，本文将详细介绍如何使用 SQL 查询来查找和处理重复记录，并提供相关的示例和代码。

查找重复记录的方法

1、使用 GROUP BY 和 HAVING 子句：通过分组和筛选来找到重复数据。

2、使用窗口函数：利用窗口函数计算附加信息，确定哪些记录是重复的。

3、使用 DISTINCT 关键字：获取无重复记录的结果集。

具体操作示例

1、使用 GROUP BY 和 HAVING 子句

假设有一个名为students 的数据表，包含以下字段：ID、name 和 age，我们想要找到具有相同名称和年龄的重复记录，可以使用以下 SQL 查询语句：

SELECT name, age, COUNT(*)
FROM students
GROUP BY name, age
HAVING COUNT(*) > 1;

这个查询将返回具有相同名称和年龄的重复记录的数量，如果表中有如下数据：

执行上述查询后，结果将是：

2、使用窗口函数

另一种查找重复记录的方法是使用窗口函数，窗口函数是一种在查询结果中计算附加信息的功能强大的工具，以下是一个使用窗口函数排名记录的示例查询：

SELECT ID, name, age, ROW_NUMBER() OVER(PARTITION BY name, age ORDER BY ID) AS row_num
FROM students
ORDER BY name, age, ID;

在这个查询中，使用 PARTITION BY 子句将相同名称和年龄的记录分组，然后使用 ROW_NUMBER 函数为每个分组中的记录排名，通过检查row_num 列，我们可以确定哪些记录是重复的，对于上述同样的数据表，执行该查询后的结果将是：

从结果中可以看到，name 为 "Alice" age 为 20 的记录在row_num 列中具有值大于1的行，这表示这些记录是重复的。

3、使用 DISTINCT 关键字

如果想要获取无重复记录的结果集，可以使用 DISTINCT 关键字：

SELECT DISTINCT * FROM students;

这种方法只能获取无重复记录的结果集，无法直接显示重复记录的详细信息，如果需要删除重复记录并保留一条，可以按以下方法操作：

SELECT DISTINCT * INTO #Tmp FROM students;
DROP TABLE students;
SELECT * INTO students FROM #Tmp;
DROP TABLE #Tmp;

这种方法通过创建一个临时表来存储不重复的记录，然后将原表删除并用临时表替代，需要注意的是，这种方法会丢失原表的其他索引和约束，因此使用时需谨慎。

处理重复记录的方法

1、删除重复记录：通常需要删除多余的重复记录，只保留一条。

2、更新重复记录：将重复记录更新为新的值。

删除重复记录的示例

1、删除全部重复记录（慎用）

DELETE FROM students
WHERE ID NOT IN (
    SELECT MIN(ID) FROM students GROUP BY name, age
);

这个查询将删除具有相同名称和年龄的重复记录中的第二条及后续记录，在我们的示例中，它将删除 ID 为 4 和 6 的两个重复记录。

2、保留一条记录

DELETE FROM students
WHERE ID NOT IN (
    SELECT MIN(ID) FROM students GROUP BY name, age
);

这个查询同样会删除多余的重复记录，只保留每组中 ID 最小的那一条记录。

3、更新重复记录

如果您不想删除重复记录，而是将其更新为新的值，可以使用以下查询：

WITH cte AS (
    SELECT ID, name, age, ROW_NUMBER() OVER(PARTITION BY name, age ORDER BY ID) AS row_num
    FROM students
)
UPDATE students
SET name = CONCAT(name, '_', row_num)
FROM cte
WHERE students.ID = cte.ID AND cte.row_num > 1;

这个查询中使用了 CTE（公用表表达式）和 CONCAT 函数，将重复记录的名称更新为原始名称后面附加一个下划线和行号，在我们的示例中，它将更新 ID 为 4 和 6 的两个重复记录的名称为 "Alice_2" 和 "Alice_3"。

相关问题与解答

1、如何在 SQL Server 中查找名字重复的数据？

回答：在 SQL Server 中，可以使用类似的方法来查找名字重复的数据，假设有一个名为Employees 的表，其中包含员工的姓名（Name），要查找名字重复的数据，可以使用以下查询：

“`sql

SELECT Name, COUNT(*) as Count

FROM Employees

GROUP BY Name

HAVING COUNT(*) > 1;

“`

这个查询将返回所有名字重复的员工及其重复次数。

2、如何删除表中基于多个字段判断的重复记录？

回答：要删除基于多个字段判断的重复记录，可以使用类似于单个字段的方法，假设有一个名为vitae 的表，其中包含字段peopleId 和seq，要删除基于这两个字段判断的重复记录，可以使用以下查询：

“`sql

DELETE FROM vitae a

WHERE (a.peopleId, a.seq) IN (

SELECT peopleId, seq FROM vitae

GROUP BY peopleId, seq

HAVING COUNT(*) > 1

) AND rowid NOT IN (

SELECT min(rowid) FROM vitae

GROUP BY peopleId, seq

HAVING COUNT(*) > 1

);

“`

这个查询将删除基于peopleId 和seq 字段判断的重复记录中的多余记录，只保留 rowid 最小的那一条记录。