聚合多行成一行（GROUP_CONCAT / STRING_AGG / LISTAGG）。

考察要点

这道题面试官想看你是否掌握了行转列中的字符串聚合能力。核心是考察你对不同数据库（尤其是 MySQL, PostgreSQL）中实现此功能的函数的熟练程度、语法细节（如排序、去重、分隔符）以及相关的性能限制和优化手段。这不仅是 SQL 编程能力的体现，也反映了你对数据库差异性和工程实践中常见问题的理解。

核心概念

字符串聚合是指将分组后的多行数据中的某个字段值，用指定的分隔符连接成一个单独的字符串。这是数据汇总和报表场景中的常见需求。

数据示例

假设我们有一张 employee_skills 表，记录了员工及其掌握的技能。

原始数据 (employee_skills)

 1CREATE TABLE employee_skills (
 2    emp_name VARCHAR(50),
 3    skill VARCHAR(50)
 4);
 5INSERT INTO employee_skills VALUES
 6('Alice', 'SQL'),
 7('Alice', 'Python'),
 8('Bob', 'Java'),
 9('Alice', 'Java'),
10('Charlie', 'SQL');

目标输出：将每个员工的技能合并为一行，按字母顺序排列。

核心函数详解

1. GROUP_CONCAT (MySQL)

   sql

   ⧉⛶

    1SELECT
    2    emp_name,
    3    GROUP_CONCAT(DISTINCT skill ORDER BY skill ASC SEPARATOR ',') AS skills
    4FROM employee_skills
    5GROUP BY emp_name;

   • DISTINCT: 去除重复的技能（如果一个员工被错误地记录了两次相同的技能）。
   • ORDER BY skill ASC: 保证拼接后的技能字符串内部是有序的，这对于结果的确定性和可读性至关重要。
   • SEPARATOR ',': 指定分隔符为逗号。

2. STRING_AGG (PostgreSQL)

   sql

   ⧉⛶

    1SELECT
    2    emp_name,
    3    STRING_AGG(DISTINCT skill, ',' ORDER BY skill) AS skills
    4FROM employee_skills
    5GROUP BY emp_name;

   • 语法更简洁，ORDER BY 直接在函数参数内。
   • DISTINCT 同样作为函数修饰符。

3. LISTAGG (Oracle)

   sql

   ⧉⛶

    1SELECT
    2    emp_name,
    3    LISTAGG(skill, ',') WITHIN GROUP (ORDER BY skill) AS skills
    4FROM (SELECT DISTINCT emp_name, skill FROM employee_skills)
    5GROUP BY emp_name;

   • ORDER BY 语法是 WITHIN GROUP (...)，比较独特。
   • 早期版本不支持 LISTAGG(DISTINCT ...)，需要通过子查询先去重。

多种解法

问题背景：同上文 employee_skills 表，聚合每个员工的技能。

解法 1：使用 GROUP_CONCAT (MySQL / MariaDB)

• 适用版本: ✅ 所有现代 MySQL / MariaDB 版本
• SQL 代码:
  sql

  ⧉⛶

   1SELECT
   2    emp_name,
   3    GROUP_CONCAT(skill ORDER BY skill SEPARATOR ',') AS skills
   4FROM
   5    employee_skills
   6GROUP BY
   7    emp_name;

• 复杂度:
  • 时间复杂度: $O(N + K \log K)$，其中 N 是总行数（用于 GROUP BY 的 Hash 聚合），K 是每个分组内的平均行数（用于组内排序）。
  • 空间复杂度: $O(M)$，M 是分组的数量，用于存储分组的中间结果。
• 索引依赖: 为了优化 GROUP BY 和 ORDER BY，在 (emp_name, skill) 上建立复合索引是最高效的。这可以实现索引覆盖扫描，并避免额外的文件排序（filesort）。

解法 2：使用 STRING_AGG (PostgreSQL / SQL Server)

• 适用版本: ✅ PostgreSQL 9.0+ / SQL Server 2017+
• SQL 代码:
  sql

  ⧉⛶

   1SELECT
   2    emp_name,
   3    STRING_AGG(skill, ',' ORDER BY skill) AS skills
   4FROM
   5    employee_skills
   6GROUP BY
   7    emp_name;

• 复杂度: 与 GROUP_CONCAT 类似，主要开销在于 GROUP BY 和组内排序。
• 索引依赖: 同样，INDEX ON employee_skills (emp_name, skill) 能提供最佳性能。

解法 3：使用递归 CTE (通用但低效，适用于无原生函数的旧版数据库)

这种方法模拟了字符串的逐步构建过程，展示了对高级 SQL 特性的理解，但生产中应优先使用原生函数。

• 适用版本: ✅ 支持递归 CTE 的数据库（如 MySQL 8.0+, PostgreSQL, SQL Server）
• SQL 代码:
  sql

  ⧉⛶

   1WITH RECURSIVE NumberedSkills AS (
   2    -- 1. 为每个员工的技能进行编号
   3    SELECT
   4        emp_name,
   5        skill,
   6        ROW_NUMBER() OVER(PARTITION BY emp_name ORDER BY skill) as rn
   7    FROM employee_skills
   8),
   9AggregatedSkills AS (
  10    -- 2. 递归构建字符串
  11    -- 基础部分：选择每个员工的第一个技能
  12    SELECT
  13        emp_name,
  14        CAST(skill AS VARCHAR(1000)) as skills,
  15        rn
  16    FROM NumberedSkills
  17    WHERE rn = 1
  18    UNION ALL
  19    -- 递归部分：将下一个技能拼接到已有字符串上
  20    SELECT
  21        curr.emp_name,
  22        CONCAT(prev.skills, ',', curr.skill),
  23        curr.rn
  24    FROM AggregatedSkills prev
  25    JOIN NumberedSkills curr ON prev.emp_name = curr.emp_name AND prev.rn + 1 = curr.rn
  26)
  27-- 3. 找出每个员工最终（最长）的拼接结果
  28SELECT
  29    emp_name,
  30    skills
  31FROM AggregatedSkills a
  32WHERE rn = (SELECT MAX(rn) FROM AggregatedSkills WHERE emp_name = a.emp_name);

• 复杂度:
  • 时间复杂度: 远高于原生聚合函数，涉及多次连接和数据扫描，性能较差。
  • 空间复杂度: 递归过程中会产生大量中间结果集，空间开销大。
• 何时使用: 仅作为一种炫技或在极其古老的、不支持原生字符串聚合函数的数据库版本中的备选方案。

常见低分回答

1. 忽略组内排序：GROUP_CONCAT(skill)。这会导致每次查询结果中的技能顺序可能不同，对于需要确定性输出的业务是致命的。
2. 忘记 group_concat_max_len 限制：直接使用 GROUP_CONCAT 而不考虑结果可能被截断。在面试中不能主动提及这个变量，说明候选人缺乏生产环境经验。默认值 1024 字节非常小，很容易超出。
3. 在客户端（应用层）聚合：SELECT emp_name, skill FROM employee_skills ORDER BY emp_name，然后由 Java/Python/Go 代码循环处理来拼接字符串。这把本应由数据库完成的工作转移到了应用层，增加了网络传输量和应用内存消耗，是典型的反模式。
4. 使用相关子查询：
   sql

   ⧉⛶

    1-- 性能极差的反例
    2SELECT
    3    DISTINCT e1.emp_name,
    4    (SELECT GROUP_CONCAT(e2.skill) FROM employee_skills e2 WHERE e2.emp_name = e1.emp_name) AS skills
    5FROM employee_skills e1;

   这个查询对外部查询的每一行都会执行一次子查询，其复杂度近似于 $O(N^2)$，数据量稍大就会导致查询超时。

工程实践与优化

• group_concat_max_len 的处理：在 MySQL 中，这是最重要的实践点。
  • 查询当前值: SHOW VARIABLES LIKE 'group_concat_max_len';
  • 临时调大: SET SESSION group_concat_max_len = 1048576; (设置为 1MB)。在代码中执行业务 SQL 前，先执行此命令。
  • 永久修改: 修改 my.cnf 配置文件（需要 DBA 权限）。
• 索引优化: GROUP BY 和 ORDER BY 的列是索引设计的关键。对于 GROUP BY emp_name ORDER BY skill，最佳索引是 (emp_name, skill)。
  • EXPLAIN 分析: 观察 Extra 列。如果看到 Using index，表示实现了覆盖索引，性能最佳。如果看到 Using temporary; Using filesort，说明 MySQL 无法利用索引完成排序和分组，创建了临时表，性能较差。需要检查索引是否正确创建和使用。
• 替代方案：JSON 聚合：如果数据库支持（MySQL 5.7+, PostgreSQL），使用 JSON 函数是更现代、更健壮的选择。
  • MySQL: JSON_ARRAYAGG(skill)
  • PostgreSQL: JSON_AGG(skill)
  • 优点: 结果是结构化的 JSON 数组，而非纯文本。它能保留原始数据类型，避免了分隔符带来的解析问题，并且数据库通常提供丰富的 JSON 查询函数。

常见追问与延伸题目

1. 追问：如果 group_concat_max_len 无法修改（没有权限），但聚合后的字符串又确实超过了长度，你该如何处理？

   • 回答要点 1 (分块聚合)：可以在 SQL 内部进行分块。通过窗口函数 ROW_NUMBER() 给技能编号，然后除以一个“块大小”（例如每 50 个技能一块），再对 emp_name 和块编号进行 GROUP BY。这样每个员工会产生多行聚合结果，应用层再将这几行拼接起来。这是一种在数据库层面减轻单次聚合压力的方法。
   • 回答要点 2 (应用层处理)：作为最后的妥协，可以将原始数据 SELECT emp_name, skill ORDER BY emp_name, skill 查询出来，在应用代码中通过流式处理或迭代的方式进行聚合。需要向面试官说明这是在数据库限制下的无奈之举，并清楚其缺点（网络开销大、应用内存占用）。

2. 追问：如何实现反向操作？即将一个逗号分隔的字符串 'Java,Python,SQL' 拆分成多行？

   • 回答要点 (MySQL)：MySQL 8.0+ 可以使用 JSON_TABLE 功能，将字符串转为 JSON 数组再展开。在旧版本中，这是一个经典的棘手问题，通常需要借助一个数字辅助表（tally table）和 SUBSTRING_INDEX 函数来循环截取。
   • 回答要点 (PostgreSQL)：非常简单，使用 UNNEST(STRING_TO_ARRAY('Java,Python,SQL', ',')) 即可，这是 PostgreSQL 的强大之处。
   • 回答要点 (SQL Server)：使用 STRING_SPLIT 函数。

3. 追问：你提到了 JSON 聚合，它和字符串聚合相比，在什么场景下更有优势？

   • 回答要点 1 (数据结构化)：当聚合的内容不只是简单字符串时，例如，你想聚合每个技能和它的熟练度 {"skill": "Python", "level": 5}。使用 JSON_ARRAYAGG(JSON_OBJECT('skill', skill, 'level', level)) 可以生成结构化的 JSON 数组，而 GROUP_CONCAT 只能生成难以解析的扁平字符串。
   • 回答要点 2 (后续处理)：如果前端或其他服务需要直接消费这些聚合数据，JSON 格式是天然的、无需解析的。如果聚合后的数据还需要在数据库中进行二次查询（例如，查询所有技能包含 "Python" 的员工），许多数据库（如 PostgreSQL）对 JSONB 类型的索引和查询支持远胜于对文本的 LIKE 查询。
   • 回答要点 3 (无歧义)：当聚合的字符串本身可能包含分隔符（例如，技能名称是 'C, C++'）时，GROUP_CONCAT 会产生歧义，而 JSON 格式会正确处理转义，没有这个问题。