找出表中只出现一次的数据。

考察要点

这道题是 SQL 面试中的 "Hello World"，看似简单，但面试官想通过它快速筛选候选人的基本功是否扎实。主要考察以下三点：

1. SQL 聚合与过滤：你是否熟练掌握 GROUP BY 分组、COUNT() 聚合函数，以及 HAVING 子句的正确用法。
2. SQL 执行顺序：你是否清楚 WHERE 和 HAVING 的本质区别，即 WHERE 在分组前过滤行，HAVING 在分组后过滤组。
3. 现代 SQL 特性：你是否了解并能运用窗口函数 (Window Functions) 解决此类问题，这能体现你的知识广度和深度，是加分项。

核心概念

要解决此问题，必须理解以下几个核心 SQL 概念：

1. GROUP BY 子句 GROUP BY 用于将结果集中的行按照一个或多个列进行分组，通常与聚合函数（COUNT, SUM, AVG 等）一起使用，对每个分组进行计算。

2. COUNT() 聚合函数 COUNT() 用于计算行数。

   • COUNT(*): 计算组内所有行数，包括 NULL 值。
   • COUNT(column): 计算组内指定列中非 NULL 值的行数。
   • COUNT(DISTINCT column): 计算组内指定列中唯一且非 NULL 值的行数。 在本题中，COUNT(*) 或 COUNT(num) 效果相同（假设 num 列不含 NULL）。

3. HAVING 子句 HAVING 用于在 GROUP BY 分组之后，对聚合结果进行过滤。这是它与 WHERE 的根本区别。

   • 执行顺序：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
   • 作用对象：WHERE 作用于原始表中的每一行；HAVING 作用于 GROUP BY 生成的中间结果（分组）。

   特性	WHERE	HAVING
   作用时机	分组聚合前	分组聚合后
   作用对象	行数据	聚合结果（组）
   能否使用聚合函数	否	是 (e.g., HAVING COUNT(*) > 1)

4. 窗口函数 COUNT() OVER() (MySQL 8.0+) 窗口函数在保留原始行的情况下，为每一行计算一个与其相关窗口（一组行）的聚合值。

   • COUNT(*) OVER (PARTITION BY column): 对 column 进行分区（类似分组），然后计算每个分区内的行数，并将该计数值作为新的一列附加到分区内的每一行上。它不合并行，保留了所有原始数据。

   数据示例： 原始表 my_numbers:

   id	num
   1	8
   2	8
   3	3
   4	1
   5	4
   6	5
   7	6

   使用 COUNT(*) OVER (PARTITION BY num) 后的中间结果：

   id	num	count_per_num
   3	3	1
   4	1	1
   5	4	1
   6	5	1
   7	6	1
   1	8	2
   2	8	2

   可以看到，原始的 7 行数据都在，只是增加了一列 count_per_num。之后只需筛选 count_per_num = 1 的行即可。

多种解法

假设我们有如下的表 my_numbers：

 1CREATE TABLE my_numbers (
 2  num INT
 3);
 4INSERT INTO my_numbers (num) VALUES (8), (8), (3), (1), (4), (5), (6);

目标：找出 3, 1, 4, 5, 6。

解法 1：GROUP BY + HAVING (最经典、通用)

这是最标准、最通用的解法，适用于所有主流数据库。

• 标注版本：✅ 所有 SQL 数据库 (MySQL, PostgreSQL, Oracle, SQL Server 等)
• SQL 代码：
  sql

  ⧉⛶

   1SELECT num
   2FROM my_numbers
   3GROUP BY num
   4HAVING COUNT(num) = 1;

• 时间/空间复杂度：
  • 时间复杂度：$O(N)$ 或 $O(N \log N)$。如果使用哈希表进行分组，复杂度为 $O(N)$；如果数据量大到内存不足需要排序，则为 $O(N \log N)$。
  • 空间复杂度：$O(K)$，其中 K 是 num 列中唯一值的数量，用于存储分组和计数的哈希表。
• 索引依赖：
  • 在 num 列上建立索引 (CREATE INDEX idx_num ON my_numbers(num);) 可以极大地提升性能。数据库可以利用索引的有序性来优化 GROUP BY 操作（如 MySQL 中的 "Loose Index Scan"），避免全表扫描和额外的排序。

解法 2：窗口函数 (现代 SQL 推荐)

这种解法更具表现力，在需要保留原始行信息的复杂场景下尤为强大。

• 标注版本：✅ MySQL 8.0+, PostgreSQL, Oracle, SQL Server；❌ MySQL 5.7 及以下版本不支持。
• SQL 代码：
  sql

  ⧉⛶

   1-- 使用 CTE (Common Table Expression) 提高可读性
   2WITH NumberCounts AS (
   3    SELECT
   4        num,
   5        COUNT(*) OVER (PARTITION BY num) AS occurrence_count
   6    FROM
   7        my_numbers
   8)
   9SELECT num
  10FROM NumberCounts
  11WHERE occurrence_count = 1;

• 时间/空间复杂度：
  • 时间复杂度：通常为 $O(N \log N)$，因为窗口函数需要对分区键进行排序。某些情况下数据库可以优化为 $O(N)$。
  • 空间复杂度：$O(N)$，因为需要为原始表的每一行都保留一个窗口计算结果。
• 索引依赖：
  • 在 num 列上的索引同样可以帮助 PARTITION BY 子句，加速数据分区的过程。

何时使用：当你不希望 GROUP BY 将多行压缩为一行，而是想在原始行上附加聚合信息时，窗口函数是最佳选择。例如，"查询所有只出现一次的数字，并返回它们在原表中的所有信息"。

解法 3：子查询 (不推荐，但可作为思路)

通过子查询先计算出所有数字的出现次数，再与原表进行连接。

• 标注版本：✅ 所有 SQL 数据库
• SQL 代码：
  sql

  ⧉⛶

   1SELECT DISTINCT T1.num
   2FROM my_numbers AS T1
   3JOIN (
   4    SELECT num
   5    FROM my_numbers
   6    GROUP BY num
   7    HAVING COUNT(num) = 1
   8) AS T2 ON T1.num = T2.num;

  注意：这个写法是冗余的，因为子查询 T2 本身已经得出了最终结果。这里仅作思路展示，实际面试中直接用解法 1 即可。更糟糕的写法是 WHERE num IN (subquery)，在某些老版本 MySQL 中性能较差。

常见低分回答

1. WHERE 和 HAVING 混淆：这是最典型的错误，直接暴露了对 SQL 执行顺序的无知。
   sql

   ⧉⛶

    1-- 错误! 聚合函数不能用在 WHERE 子句中
    2SELECT num, COUNT(num)
    3FROM my_numbers
    4WHERE COUNT(num) = 1
    5GROUP BY num;

2. SELECT 非聚合列而没有 GROUP BY：在严格模式下（如 PostgreSQL 或标准 SQL），这会直接报错。
   sql

   ⧉⛶

    1-- 错误! 'num' 列必须出现在 GROUP BY 子句中或在聚合函数中使用
    2SELECT num, COUNT(*)
    3FROM my_numbers;

3. 误用 COUNT(列) vs COUNT(*)：虽然在本题中 COUNT(num) 和 COUNT(*) 结果一致（假设 num 非空），但候选人应清楚它们的细微差别。如果 num 列可能为 NULL，COUNT(num) 会忽略 NULL 行，可能导致逻辑错误。COUNT(*) 是最稳妥的选择。
4. 忽略 NULL 的特殊语义：如果表中存在 NULL 值，GROUP BY 会将所有 NULL 视为一组。COUNT(NULL) 结果为 0，但 COUNT(*) 会计算 NULL 所在的行。候选人应能讨论 NULL 对结果的影响。

工程实践与优化

• 索引是关键：对于千万级以上的数据，num 列上必须有索引。没有索引的 GROUP BY 会导致全表扫描 (Full Table Scan) 和文件排序 (filesort)，这是灾难性的。
• 查看执行计划：面试时可以说 "我会用 EXPLAIN 来分析这条 SQL 的执行计划"。
  • 理想情况 (GROUP BY): EXPLAIN 结果中 Extra 字段显示 Using index for group-by 或 Loose Index Scan。这表示 MySQL 直接利用索引结构完成了分组，无需读取数据行，性能极高。
  • 次优情况：Extra 字段显示 Using index，表示进行了覆盖索引扫描。
  • 糟糕情况：Extra 字段显示 Using temporary; Using filesort。这表示 MySQL 需要创建临时表，并在磁盘上进行排序来完成分组，性能很差。
• 数据倾斜：如果某个 num 值出现次数远超其他值，可能会导致 GROUP BY 或 PARTITION BY 的数据倾斜，单个线程/节点负载过高。在分布式数据库中，这需要特殊处理（如添加随机前缀 salt）。
• 预聚合：如果 "查找只出现一次的数据" 是一个高频请求，但数据写入不频繁，可以考虑建立一个物化视图或汇总表，定时任务（如每晚）计算好每个数字的频次。这样，查询请求可以直接命中汇总表，实现毫秒级响应。

常见追问与延伸题目

1. 追问：如果数据量增长 100 倍达到 10 亿级别，你的 SQL 还能跑吗？怎么优化？

   • 回答要点 1 (单机 MySQL)：首先，GROUP BY 方案在有合适索引的情况下依然是理论上最优的。但 10 亿级别数据，GROUP BY 聚合过程会消耗大量内存和 CPU，甚至可能因为中间结果集太大而频繁刷盘，导致查询变得极慢或超时。优化方向是：
     • 硬件：增加服务器内存（innodb_buffer_pool_size, tmp_table_size）。
     • 分治：如果业务允许，可以按时间范围或其他分区键分批处理，例如 SELECT ... FROM my_numbers WHERE created_time BETWEEN ... AND ... GROUP BY ...，然后由应用层合并结果。
   • 回答要点 2 (架构升级)：对于这个数据量级，更现实的方案是采用大数据技术栈。我会将数据导入到如 Hive, Spark, ClickHouse 等分布式计算平台。这个问题在 MapReduce 模型中非常经典：
     • Map 阶段：以 num 为 key，1 为 value，输出 (num, 1)。
     • Reduce 阶段：对相同 key 的 value 进行求和，得到 (num, count)。
     • 最后再过滤出 count = 1 的结果。这个过程可以水平扩展，轻松处理 PB 级数据。

2. 追问：如果要查找出现次数 Top K 的数字，SQL 怎么写？

   • 回答要点：这是一个 Top-K 问题，非常经典。只需在解法 1 的基础上增加 ORDER BY 和 LIMIT。
     sql

     ⧉⛶

      1SELECT num, COUNT(num) AS occurrence_count
      2FROM my_numbers
      3GROUP BY num
      4ORDER BY occurrence_count DESC
      5LIMIT 10; -- 查找出现次数最多的前 10 个数字

     这考察了对 ORDER BY 和 LIMIT 的熟练运用。

3. 追问：如果要找出表中第一个只出现一次的数字呢？（按原表顺序）

   • 回答要点：这个问题 GROUP BY 就很难处理了，因为它破坏了原始的顺序信息。这正是窗口函数的优势所在。假设表有一个自增 id 列来表示顺序。
     sql

     ⧉⛶

      1-- 必须使用窗口函数
      2SELECT num
      3FROM (
      4    SELECT
      5        num,
      6        COUNT(*) OVER (PARTITION BY num) as cnt,
      7        ROW_NUMBER() OVER (ORDER BY id) as rn -- 保留原始顺序
      8    FROM my_numbers
      9) AS temp
     10WHERE cnt = 1
     11ORDER BY rn ASC -- 按原始顺序排序
     12LIMIT 1;

     这个问题能很好地区分候选人是否真正理解了不同解法的适用场景和局限性。