SQL 实战编程题（GitHub 高频题） · 数据透视 / 行列转换

把一列以逗号分隔的字符串拆成多行。(MySQL 8 / PostgreSQL string_to_array)

Split a column of comma-separated strings into multiple rows. (MySQL 8 / PostgreSQL string_

答案语言

考察要点

这道题面试官想看你是否掌握了处理非规范化数据的 SQL 高级技巧，以及对数据库范式的理解。核心考察点包括：

现代 SQL 特性：是否熟悉窗口函数、递归 CTE（Common Table Expressions）、以及特定数据库（如 PostgreSQL, MySQL 8.0+）提供的原生字符串/JSON 处理函数。
数据结构转换能力：能否将一行内的多个值（宽表）高效地转换为多行单值（长表）的逻辑思维。
性能意识和设计思想：是否能认识到存储逗号分隔字符串本身是一种反模式（violates 1NF），并能提出更优的数据库设计方案。

核心概念

解决此问题的核心是行生成，即根据一行中的信息派生出多行。这通常依赖于特定函数或 SQL 结构。

1. 递归公用表表达式 (Recursive CTE) 一种标准的 SQL 方法，通过定义一个初始查询（锚点成员）和一个引用自身的递归查询（递归成员），迭代地生成结果集。

结构:

sql

 1WITH RECURSIVE cte_name (col1, col2, ...) AS (
 2    -- 锚点成员: 递归的初始行
 3    SELECT ... FROM ...
 4    UNION ALL
 5    -- 递归成员: 引用 cte_name 自身，生成新行
 6    SELECT ... FROM cte_name WHERE [终止条件]
 7)
 8SELECT * FROM cte_name;

工作原理:
1. 执行锚点查询，生成初始结果集 R0。
2. 执行递归查询，输入为上一步的结果集 Ri，生成新的结果集 Ri+1。
3. 重复步骤 2，直到递归查询返回空集（即满足终止条件）。
4. UNION ALL 将所有中间结果集 (R0, R1, R2, ...) 组合成最终的 CTE 结果。

2. PostgreSQL: string_to_array & unnest 这是 PostgreSQL 中处理此类问题的标准、高效方法。

string_to_array(string, delimiter): 将一个字符串按指定的分隔符拆分成一个数组（ARRAY）。
unnest(array): 一个强大的集合返回函数，它将一个数组的每个元素扩展成单独的一行。

示例数据: 假设我们有下表 products：

id	name	tags
1	SuperPhone X	'electronics,phone,smart'
2	Laptop Pro	'electronics,computer'
3	Simple Mug	'kitchen,home'

期望输出:

id	name	tag
1	SuperPhone X	electronics
1	SuperPhone X	phone
1	SuperPhone X	smart
2	Laptop Pro	electronics
2	Laptop Pro	computer
3	Simple Mug	kitchen
3	Simple Mug	home

3. MySQL 8.0+: JSON_TABLE MySQL 8.0 引入了强大的 JSON 函数，JSON_TABLE 可以将 JSON 文档转换为关系表格式。虽然我们的输入是字符串，但可以通过简单的转换将其变为 JSON 数组，再利用此函数。

语法: JSON_TABLE(json_doc, path COLUMNS (column_definitions)).
思路: 'a,b,c' -> CONCAT('["', REPLACE('a,b,c', ',', '","'), '"]') -> '["a","b","c"]' (JSON 数组字符串)。然后 JSON_TABLE 可以解析这个数组。

多种解法

解法 1：递归 `CTE` (通用标准解法)

此方法逻辑清晰，兼容性好，是展示 SQL 功底的不错选择。

适用版本: ✅ MySQL 8.0+, PostgreSQL, SQL Server 等。

SQL 代码:

sql

 1WITH RECURSIVE split_tags (id, name, tag, remaining_tags) AS (
 2  -- 锚点成员：取出第一个 tag
 3  SELECT
 4    id,
 5    name,
 6    -- SUBSTRING_INDEX 在找到第一个逗号前截取字符串
 7    SUBSTRING_INDEX(tags, ',', 1) AS tag,
 8    -- 截取剩余部分
 9    SUBSTRING(tags, LOCATE(',', tags) + 1) AS remaining_tags
10  FROM products
11  WHERE LOCATE(',', tags) > 0 -- 只处理有多个 tag 的行
12
13  UNION ALL
14
15  -- 递归成员：在剩余的 tags 字符串上重复操作
16  SELECT
17    id,
18    name,
19    SUBSTRING_INDEX(remaining_tags, ',', 1) AS tag,
20    SUBSTRING(remaining_tags, LOCATE(',', remaining_tags) + 1) AS remaining_tags
21  FROM split_tags
22  WHERE LOCATE(',', remaining_tags) > 0
23
24  UNION ALL
25
26  -- 收尾：处理每个原始行中最后一个 tag 或只有一个 tag 的情况
27  SELECT
28    id,
29    name,
30    remaining_tags,
31    ''
32  FROM split_tags
33  WHERE LOCATE(',', remaining_tags) = 0 AND remaining_tags != ''
34  UNION ALL
35  SELECT
36    id,
37    name,
38    tags,
39    ''
40  FROM products
41  WHERE LOCATE(',', tags) = 0
42)
43SELECT id, name, TRIM(tag) AS tag FROM split_tags ORDER BY id, tag;

注意：上述实现较为复杂，一个更简洁的递归 CTE 写法如下，它通过增加一个逗号来统一处理边界条件。

sql

 1-- 更优雅的递归 CTE 写法
 2WITH RECURSIVE cte AS (
 3  SELECT
 4    id,
 5    name,
 6    -- 在末尾加一个逗号，简化对最后一个元素的处理
 7    CONCAT(tags, ',') AS tags_padded,
 8    1 AS start_pos,
 9    LOCATE(',', CONCAT(tags, ',')) AS end_pos
10  FROM products
11
12  UNION ALL
13
14  SELECT
15    id,
16    name,
17    tags_padded,
18    end_pos + 1,
19    LOCATE(',', tags_padded, end_pos + 1)
20  FROM cte
21  WHERE end_pos > 0 AND LOCATE(',', tags_padded, end_pos + 1) > 0
22)
23SELECT
24  id,
25  name,
26  SUBSTRING(tags_padded, start_pos, end_pos - start_pos) AS tag
27FROM cte;

复杂度: 时间复杂度与总标签数成正比，约为 $O(\sum_{i=1}^{M} N_i)$ ，其中 M 是总行数，N_i 是第 i 行的标签数。空间复杂度也与总标签数相关。
索引依赖: 无法直接利用 tags 列上的索引来加速拆分过程。但对 id 列的索引有助于初始数据检索和最终排序。

解法 2：PostgreSQL 原生函数

这是 PostgreSQL 中最高效、最简洁的解法。

适用版本: ✅ PostgreSQL。

SQL 代码:

sql

 1SELECT
 2  p.id,
 3  p.name,
 4  TRIM(tag) AS tag
 5FROM
 6  products p,
 7  -- 使用 unnest 将 string_to_array 的结果展开为多行
 8  LATERAL unnest(string_to_array(p.tags, ',')) AS tag;

复杂度: 时间复杂度 $O(L)$ ，其中 L 是 tags 列所有字符串的总长度。由高度优化的 C 函数实现，性能极佳。
索引依赖: 无。性能瓶颈在于字符串处理本身。

解法 3：MySQL 8.0+ JSON_TABLE

这是 MySQL 8.0 及以上版本推荐的现代解法，性能优于递归 CTE。

适用版本: ✅ MySQL 8.0+。

SQL 代码:

sql

 1SELECT
 2  p.id,
 3  p.name,
 4  jt.tag
 5FROM
 6  products p
 7CROSS JOIN
 8  -- 1. 将 'a,b,c' 转换为 '["a","b","c"]'
 9  -- 2. 使用 JSON_TABLE 将 JSON 数组的每个元素映射为一行
10  JSON_TABLE(
11    CONCAT('["', REPLACE(p.tags, ',', '","'), '"]'),
12    '$[*]' COLUMNS (
13      tag VARCHAR(255) PATH '$'
14    )
15  ) AS jt;

复杂度: 时间复杂度 $O(L)$ ，其中 L 是 tags 列所有字符串的总长度。利用了 MySQL 内建的、高效的 JSON 解析引擎。
索引依赖: 无。

常见低分回答

指出问题但不解决：“这种设计不符合第一范式，应该用关联表”。虽然这是正确的架构建议，但面试官想考察你解决当前问题的 SQL 能力。你应该在给出 SQL 解法后，再补充这一点作为优化建议。
使用 LIKE 进行模糊匹配：SELECT * FROM products WHERE tags LIKE '%phone%'。这完全没有解决“拆分”问题，而且会误匹配（如 SuperPhone），同时导致索引失效和性能问题。
忽略边界条件：写的 SUBSTRING_INDEX 逻辑只处理了中间的元素，但没有正确处理第一个或最后一个元素。或者当 tags 列为 NULL、空字符串 '' 或只有一个标签时，逻辑出错。
在应用层解决：“我用 Python/Java 来处理会更简单”。这回避了 SQL 问题本身，表明候选人 SQL 能力不足或不愿意深入。
对 COUNT(*) vs COUNT(column) 的误解：在后续分析中，如果被问到统计每个标签有多少产品，可能会误用 COUNT(tags)，这会忽略 tags 为 NULL 的行，而 COUNT(*) 或 COUNT(1) 则不会。

工程实践与优化

根本性优化：数据库范式：在生产环境中，最正确的做法是修正数据库设计。废弃 tags 列，创建两个新表：
- tags 表：tag_id INT PK, tag_name VARCHAR(255) UNIQUE
- product_tags 关联表：product_id INT, tag_id INT，并建立 (product_id, tag_id) 的复合主键或唯一索引。这样做可以获得极高的查询性能、数据完整性（避免标签拼写错误）和可维护性。
执行计划分析 (EXPLAIN)：
- 递归 CTE：EXPLAIN 会显示一个派生表（derived），其 Extra 字段可能包含 Recursive。你需要关注递归的次数（等于行数）和每次迭代处理的数据量。
- JSON_TABLE / unnest：执行计划会直接显示这些函数调用。通常这类解法的 type 可能是 ALL（全表扫描 products），这是不可避免的，因为每一行都需要被处理。关键在于函数本身的执行效率。
性能权衡：如果无法修改表结构（例如，在操作第三方系统或历史遗留系统），并且拆分查询非常频繁，可以考虑：
- 物化视图 (PostgreSQL) 或 触发器 (MySQL)：创建一个独立的、规范化的 product_tags_normalized 表。使用触发器在 products 表发生 INSERT 或 UPDATE 时，自动更新这个规范化表。所有查询都直接针对这个新表，从而避免了运行时的昂贵拆分操作。
处理脏数据：真实世界的数据往往不干净，如 ' electronics, phone , smart '。在拆分后，务必使用 TRIM() 函数清理每个标签前后的空格。对于更复杂的场景，可能需要 REGEXP_REPLACE。

常见追问与延伸题目

追问：你提到了修改表结构是最好的方案。为什么说存储逗号分隔的列表是“反模式”？请具体说明。
- 回答要点 1 (违反第一范式)：关系数据库的第一范式（1NF）要求所有字段都是原子的，不可再分。逗号分隔的字符串显然不是原子的，它包含了多个逻辑上的值。
- 回答要点 2 (查询与性能)：无法有效查询。WHERE tag = 'phone' 这样的简单查询变得不可能，只能用 LIKE '%phone%'，这既不准确又无法使用索引，导致全表扫描。
- 回答要点 3 (数据完整性)：难以维护数据一致性。同一个标签可能出现多种拼写（如 'electronic' vs 'electronics'），无法通过外键约束来保证。
- 回答要点 4 (更新与扩展)：更新操作复杂且低效。添加或删除一个标签需要读取整个字符串，进行修改，然后再写回，容易产生并发问题。字符串本身也有长度限制。
追问：如果 products 表有 10 亿行，每行的 tags 字段平均有 3-5 个标签。哪种解法性能最好？为什么？
- 回答要点 1 (原生函数最优)：PostgreSQL 的 unnest 或 MySQL 8.0+ 的 JSON_TABLE 性能会远超递归 CTE。因为这些原生函数是用 C/C++ 实现的，执行效率极高，并且是为集合操作设计的，能更好地利用 CPU 和内存。
- 回答要点 2 (递归 CTE 的瓶颈)：递归 CTE 在这种规模下会非常慢。它的执行模式类似于对每一行进行一次循环（或 LATERAL 操作），10 亿行意味着 10 亿次独立的递归启动和迭代，开销巨大。它更适合处理具有层次结构且深度较大的少量数据，而不是处理海量的宽表数据。
- 回答要点 3 (ETL 方案)：对于如此巨大的数据量，最佳实践是进行一次性的 ETL (Extract, Transform, Load) 过程。编写一个脚本（可以使用 SQL，但更可能是 Spark/Flink 等大数据工具），将这 10 亿行数据转换并加载到一个新的、规范化的表中。之后的所有查询都基于新表进行，这才是可行的工程方案。
追问：如果分隔符不是逗号，而是其他字符（比如 |），或者数据里有不规则的空格（如 'tag1 | tag2,tag3'），你的 SQL 需要怎么调整？
- 回答要点 1 (更换分隔符)：所有解法都可以轻松适应。string_to_array(tags, '|')，SUBSTRING_INDEX(tags, '|', ...)，REPLACE(tags, '|', '","')。这只是一个参数的改变。
- 回答要点 2 (处理不规则空格)：这需要更强的清洗能力。首先，可以使用 REPLACE(tags, ' ', '') 去掉所有空格，但这可能误伤带空格的标签名。更稳妥的方法是：
  - PostgreSQL: 使用 regexp_split_to_array(tags, '\s*[,|]\s*')。这个正则表达式可以匹配逗号或竖线，以及它们周围任意数量的空白字符。
  - MySQL/通用: 在拆分出每个 tag 之后，应用 TRIM() 函数，例如 SELECT TRIM(tag) FROM ...。这是最简单和通用的方法。
  - 组合处理: 如果分隔符本身也混乱，可以先用 REPLACE 将所有可能的分隔符统一成一个（如都换成逗号），然后再进行拆分。例如 REPLACE(REPLACE(tags, '|', ','), ';', ',')。这展示了处理复杂脏数据的能力。

考察要点

核心概念

多种解法

解法 1：递归 CTE (通用标准解法)

解法 2：PostgreSQL 原生函数

解法 3：MySQL 8.0+ JSON_TABLE

常见低分回答

工程实践与优化

常见追问与延伸题目

解法 1：递归 `CTE` (通用标准解法)