如何通过SQL查询找出数据库中的重复数据？

SQL 查询重复数据

在数据库管理中，有时需要查找表中的重复数据，这些重复数据可能是由于数据录入错误、数据同步问题或其他原因导致的，SQL 提供了多种方法来查找和处理重复数据，本文将介绍如何使用 SQL 查询重复数据，并提供一些示例和最佳实践。

1. 使用 GROUP BY 和 HAVING 子句查找重复数据

示例表结构

假设我们有一个名为employees 的表，其结构如下：

查询重复的部门名称

要查找department 列中出现次数超过一次的部门名称，可以使用以下 SQL 查询：

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

这个查询会返回所有出现次数超过一次的部门名称及其出现次数。

查询重复的员工姓名（区分大小写）

如果需要查找name 列中重复的员工姓名（区分大小写），可以这样做：

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

这将返回所有重复的员工姓名及其出现次数。

2. 查找重复数据的行

有时，我们不仅想知道哪些值是重复的，还想知道具体的行，下面是一个示例，展示如何查找employees 表中所有重复的行。

使用窗口函数和 ROW_NUMBER()

我们可以使用窗口函数ROW_NUMBER() 为每一行分配一个唯一的行号，然后找出具有相同值的行。

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name, department ORDER BY id) as row_num
    FROM employees
)
SELECT *
FROM CTE
WHERE row_num > 1;

这个查询首先使用ROW_NUMBER() 函数为每个具有相同name 和department 的组合分配一个行号，选择行号大于 1 的行，即重复的行。

3. 删除重复数据

在找到重复数据后，可能需要将其删除，以下是删除重复数据的一种方法。

保留最低 ID 的行并删除其他重复行

假设我们希望保留id 最小的那一行，并删除其他重复行，可以使用以下步骤：

1、找出需要删除的行的 ID。

2、删除这些行。

Step 1: 找出需要删除的行的 ID
WITH CTE AS (
    SELECT id, name, department,
           ROW_NUMBER() OVER (PARTITION BY name, department ORDER BY id) as row_num
    FROM employees
)
SELECT id INTO #temp
FROM CTE
WHERE row_num > 1;
Step 2: 删除这些行
DELETE FROM employees
WHERE id IN (SELECT id FROM #temp);
清理临时表
DROP TABLE #temp;

这个查询首先创建一个临时表#temp，其中包含需要删除的行的 ID，使用这些 ID 删除重复的行，最后清理临时表。

4. 相关问题与解答

问题 1：如何在不使用窗口函数的情况下查找重复数据？

解答：如果不使用窗口函数，可以通过自连接来实现，要查找employees 表中重复的部门名称，可以使用以下查询：

SELECT a.department, COUNT(*)
FROM employees a
JOIN employees b ON a.department = b.department AND a.id <> b.id
GROUP BY a.department;

这个查询通过自连接employees 表，并确保连接条件中的id 不同，从而找到具有相同department 但不同id 的行，即重复的部门名称。

问题 2：如何处理包含 NULL 值的重复数据？

解答：当处理包含 NULL 值的列时，需要在比较时进行特殊处理，要查找employees 表中salary 列中重复的值（包括 NULL），可以使用以下查询：

SELECT salary, COUNT(*)
FROM employees
GROUP BY salary IS NULL, COALESCE(salary, 'NULL')
HAVING COUNT(*) > 1;

这个查询通过检查salary 是否为 NULL 并将其与其他非 NULL 值一起进行分组，从而正确处理包含 NULL 值的重复数据。