找出表中重复出现的数据。

考察要点

这道题看似简单，但面试官想考察你对 SQL 数据聚合和筛选能力的掌握深度。核心是看你是否能熟练运用 GROUP BY ... HAVING 和 窗口函数 这两种核心武器。同时，通过这道题还能延伸考察你对 SQL 执行效率、索引优化以及处理大规模数据问题的思考能力。

核心概念

解决此问题的核心在于“分组”与“计数”。我们需要按可能重复的列将数据分组，然后统计每个组内的行数，最后筛选出行数大于 1 的组。

1. GROUP BY 与 HAVING

• GROUP BY: 将表中的行按照指定的列（或多列）进行分组，所有在分组列上具有相同值的行会被聚合到一条记录中。
• HAVING: 用于在 GROUP BY 聚合之后对结果集进行过滤。WHERE 子句在聚合前过滤行，而 HAVING 在聚合后过滤组。
• 执行顺序: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY。

数据示例: 假设我们有一张 employees 表：

使用 GROUP BY email HAVING COUNT(*) > 1 可以找出重复的 email 值：

 1SELECT email, COUNT(*) AS count
 2FROM employees
 3GROUP BY email
 4HAVING COUNT(*) > 1;

输出:

2. 窗口函数 (Window Functions)

• 定义: 窗口函数对一组与当前行相关的表行（这个组被称为“窗口”或“分区”）执行计算。与聚合函数不同，它不会将多行压缩为一行，而是为结果集中的每一行返回一个值。
• PARTITION BY: 这是窗口函数中的 GROUP BY。它将行分成多个分区，函数在每个分区内独立计算。
• 常用函数: ROW_NUMBER(), RANK(), DENSE_RANK(), COUNT() OVER (...)。

数据示例: 使用 COUNT(*) OVER (PARTITION BY email) 可以为每一行计算其所在分区的总行数。

 1SELECT id, name, email,
 2       COUNT(*) OVER (PARTITION BY email) AS email_count
 3FROM employees;

输出:

通过筛选 email_count > 1 的行，即可得到所有重复的记录。

多种解法

假设问题是：找出 employees 表中 email 重复的所有记录。

解法 1：GROUP BY + HAVING + JOIN / IN

这个思路是先找出哪些 email 是重复的，然后再根据这些 email 值捞出所有原始记录。

• 标注版本：✅ 所有 SQL 数据库通用。
• SQL 实现:

 1-- 使用 IN 子查询
 2SELECT id, name, email
 3FROM employees
 4WHERE email IN (
 5    SELECT email
 6    FROM employees
 7    GROUP BY email
 8    HAVING COUNT(*) > 1
 9);
10
11-- 使用 JOIN (通常性能更优)
12SELECT e1.id, e1.name, e1.email
13FROM employees e1
14JOIN (
15    SELECT email
16    FROM employees
17    GROUP BY email
18    HAVING COUNT(*) > 1
19) AS duplicates ON e1.email = duplicates.email;

• 时间/空间复杂度:
  • GROUP BY 通常需要对 email 列进行排序或哈希，复杂度为 $O(N \log N)$ 或 $O(N)$。
  • JOIN 或 IN 子查询的查找，如果 email 列有索引，则效率很高。
  • 整体复杂度主要取决于 GROUP BY 的操作。
• 索引依赖: 在 email 列上建立索引可以极大地加速 GROUP BY 和 JOIN / IN 的过程。

解法 2：窗口函数

这是更现代、更简洁的解法，一步到位，无需自连接或子查询。

• 标注版本：✅ MySQL 8.0+ / PostgreSQL / Oracle / SQL Server；❌ MySQL 5.7 不支持。
• SQL 实现:

 1-- 使用 CTE (通用表表达式) 提高可读性
 2WITH NumberedRows AS (
 3    SELECT
 4        id,
 5        name,
 6        email,
 7        -- 计算每个 email 分区内的行数
 8        COUNT(*) OVER (PARTITION BY email) AS email_count,
 9        -- 也可以用 ROW_NUMBER() 来标记，方便后续删除操作
10        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
11    FROM
12        employees
13)
14SELECT
15    id,
16    name,
17    email
18FROM
19    NumberedRows
20WHERE
21    email_count > 1;

• 时间/空间复杂度: 窗口函数通常需要对 PARTITION BY 的列进行排序，时间复杂度为 $O(N \log N)$。它需要额外的空间来存储窗口计算的中间结果。
• 索引依赖: 在 email 列上建立索引可以加速 PARTITION BY 的排序过程。

解法 3：自连接 (Self-Join)

这是一种比较“古典”的解法，在没有窗口函数的旧版本数据库中可以使用。逻辑是：对于表中的每一行，去查找是否存在另一行，它们的 email 相同但 id 不同。

• 标注版本：✅ 所有 SQL 数据库通用。
• SQL 实现:

 1SELECT DISTINCT e1.*
 2FROM employees e1
 3JOIN employees e2
 4  ON e1.email = e2.email AND e1.id <> e2.id;

• 时间/空间复杂度: 如果没有索引，这是一个笛卡尔积式的比较，复杂度高达 $O(N^2)$，性能极差。即使 email 和 id 上有索引，其性能通常也不如前两种方法。DISTINCT 操作也需要额外的排序或哈希开销。
• 索引依赖: 必须在 email 和 id 列上建立索引，否则对于大数据量是灾难性的。

解法对比：

• 推荐度: 窗口函数 > GROUP BY > 自连接。
• 性能: 对于大多数现代数据库，窗口函数和 GROUP BY 性能相当，具体取决于优化器。自连接通常最慢。
• 可读性与扩展性: 窗口函数代码最清晰，且非常容易扩展（例如，要删除重复项只保留一个，只需在 WHERE 条件中用 rn > 1）。

常见低分回答

1. 只找出重复的值，没找出重复的行：SELECT email FROM employees GROUP BY email HAVING COUNT(*) > 1; 这个答案不完整，因为它只返回了 ['[email protected]', '[email protected]']，而题目要求找出所有重复的记录行。
2. 误用 WHERE 代替 HAVING：SELECT email, COUNT(*) FROM employees GROUP BY email WHERE COUNT(*) > 1; 这是语法错误，因为 WHERE 子句在 GROUP BY 之前执行，不能使用聚合函数。
3. 使用极其低效的关联子查询：SELECT * FROM employees e1 WHERE (SELECT COUNT(*) FROM employees e2 WHERE e2.email = e1.email) > 1; 这个查询对主表的每一行都会执行一次子查询，导致 $O(N^2)$ 级别的计算量，数据量稍大就会超时。
4. 自连接时忘记排除自身：SELECT e1.* FROM employees e1 JOIN employees e2 ON e1.email = e2.email; 这会把每一行都和自己匹配上，导致所有记录都被错误地认为是重复的。必须加上 e1.id <> e2.id 或 e1.id < e2.id 这样的条件。
5. 忽略复合唯一键：如果重复是由多个列共同决定的（例如 first_name 和 last_name），仍然只按单一列（如 email）查找，这没有完全理解业务场景。正确的做法是 GROUP BY first_name, last_name 或 PARTITION BY first_name, last_name。

工程实践与优化

• 索引是关键：无论哪种方案，在用于判断重复的列（如 email）上建立索引是性能优化的第一步。对于 GROUP BY 和 PARTITION BY，索引可以避免全表扫描和昂贵的 filesort。
• 查看执行计划：使用 EXPLAIN 分析你的查询。
  • 对于 GROUP BY，理想的 Extra 字段应该看到 Using index，避免 Using temporary; Using filesort。
  • 对于 JOIN，确保 type 是 ref 或 eq_ref，而不是 ALL。
• 删除重复数据：这是最常见的后续操作。使用窗口函数是目前最安全和高效的方式。
  sql

  ⧉⛶

   1-- MySQL 8.0+
   2WITH CTE AS (
   3    SELECT id,
   4           ROW_NUMBER() OVER(PARTITION BY email ORDER BY id ASC) as rn
   5    FROM employees
   6)
   7DELETE FROM employees
   8WHERE id IN (SELECT id FROM CTE WHERE rn > 1);

  注意：在生产环境执行 DELETE 前，务必开启事务 (START TRANSACTION;)，并先用 SELECT 验证要删除的数据是否正确，确认无误后再执行 DELETE 和 COMMIT。
• 预防胜于治疗：在表设计阶段，就应该为需要保持唯一的列（如 email）添加 UNIQUE 约束。ALTER TABLE employees ADD UNIQUE INDEX idx_uniq_email (email); 这能从根本上防止脏数据产生。

常见追问与延伸题目

1. 追问：如果要删除这些重复数据，只保留 id 最小的那一条，你会怎么写 SQL？

   • 回答要点: 这正是窗口函数 ROW_NUMBER() 的用武之地。我会使用一个 CTE，通过 PARTITION BY 对重复的列分组，并按 id 升序 (ORDER BY id ASC) 生成行号 rn。这样，每个分组中 id 最小的记录 rn 就是 1，其余的 rn 都大于 1。最后，我只需要 DELETE 掉 rn > 1 的所有记录即可。SQL 如上“工程实践”部分所示。

2. 追问：如果这张表有 10 亿行，内存有限，你的 GROUP BY 或窗口函数查询可能会因为排序而耗尽内存或临时磁盘空间。你会如何处理？

   • 回答要点:
     • 分治策略：我不会尝试一次性对 10 亿行数据进行 GROUP BY。我会采用分而治之的策略。例如，如果 email 的基数很大，我可以按 email 的哈希值或首字母进行切分，WHERE email LIKE 'a%'，分批次地找出重复项。
     • 外部工具/大数据方案：对于这个量级的数据，更专业的做法是使用大数据处理框架，如 Spark SQL 或 Hive。它们天生为分布式计算和处理海量数据而设计，能够将数据分散到多台机器上并行计算 GROUP BY 或 PARTITION BY，避免单点内存瓶颈。
     • 优化数据库配置：如果必须在单机数据库上处理，我会检查并调优 sort_buffer_size, tmp_table_size, max_heap_table_size 等参数，但这只是缓兵之jg，不能根本解决问题。

3. 追问：GROUP BY 和窗口函数 PARTITION BY 在查找重复项时，你更倾向于用哪个？为什么？

   • 回答要点:
     • 场景决定: 如果我只需要知道哪些值是重复的，或者只需要重复项的计数，我会用 GROUP BY ... HAVING，因为它意图明确，且在某些数据库版本中可能优化得更好。
     • 获取完整行: 如果我需要获取所有重复的完整行记录，或者需要进行“保留一行，删除其他”的操作，我绝对会选择窗口函数。因为它可以在一次扫描中完成标记，代码更简洁，逻辑更清晰，避免了 GROUP BY 之后再进行一次 JOIN 或 IN 子查询，通常性能也更好。
     • 现代 SQL 标准: 窗口函数是更现代、更强大的 SQL 特性，展现了对新标准 SQL 的熟悉程度，在面试中是加分项。