本页内容受版权保护 · 已添加水印 · 禁止任何形式转载
SQL 实战编程题（GitHub 高频题） · 数据透视 / 行列转换

动态行转列：科目数量未知，怎么写？

Dynamic pivot: How to write it when the number of columns is unknown?

答案语言

考察要点

这道题面试官想看你是否理解 SQL 的静态本质以及如何突破这种限制。核心考察点包括：

  1. 静态 vs 动态 SQL：是否明白标准 SQL 的 SELECT 列数和列名必须在查询解析阶段就确定，无法根据数据内容动态生成。
  2. 程序化 SQL 能力：是否掌握通过存储过程、函数等数据库内编程方式，或者在应用层（如 Python/Java）拼接 SQL 字符串来实现动态需求。
  3. 数据库特性掌握：是否了解特定数据库（如 MySQL）提供的 GROUP_CONCAT 等高级函数，以及如何利用它们巧妙地解决问题。同时，对 SQL 注入等安全问题有足够认识。

核心概念

解决此问题的核心是理解“动态”的实现原理，它本质上是一个“两步走”的过程：先查询元数据，再根据元数据生成最终的查询语句。

1. 静态行转列（已知科目）

当要转换的列（科目）是已知且固定的，我们可以使用 CASE WHEN 结合聚合函数来实现。这是动态行转列的基础。

数据示例 假设有学生成绩表 tb_scores：

sql
 1CREATE TABLE tb_scores (
 2    student_id INT,
 3    student_name VARCHAR(50),
 4    subject_name VARCHAR(50),
 5    score DECIMAL(5, 2)
 6);
 7

 8INSERT INTO tb_scores VALUES
 9(1, '张三', '语文', 88.00),
10(1, '张三', '数学', 95.00),
11(1, '张三', '英语', 78.00),
12(2, '李四', '语文', 92.00),
13(2, '李四', '数学', 85.00),
14(2, '李四', '物理', 98.00);

目标输出 将每个 subject_name 转换为一列：

student_idstudent_name语文数学英语物理
1张三88.0095.0078.00NULL
2李四92.0085.00NULL98.00

静态 SQL 实现

sql
 1SELECT
 2    student_id,
 3    student_name,
 4    MAX(CASE WHEN subject_name = '语文' THEN score ELSE NULL END) AS '语文',
 5    MAX(CASE WHEN subject_name = '数学' THEN score ELSE NULL END) AS '数学',
 6    MAX(CASE WHEN subject_name = '英语' THEN score ELSE NULL END) AS '英语',
 7    MAX(CASE WHEN subject_name = '物理' THEN score ELSE NULL END) AS '物理'
 8FROM
 9    tb_scores
10GROUP BY
11    student_id, student_name;

这里的 MAX 或 SUM 都可以，因为 GROUP BY 后每个学生每个科目组合只有一条记录，聚合函数的作用是将其从行中“提取”到列的位置。

2. 动态行转列原理

当 subject_name 未知或会变化时，上述静态 SQL 无法使用。因为我们无法预知 CASE WHEN 需要写多少个，以及它们的列别名是什么。

动态行转列的本质是用程序生成上面的静态 SQL。

两步执行流程：

  1. 查询列名：从数据表中查询出所有需要转换为列的唯一值。

    sql
     1-- 步骤一：获取所有唯一的科目名称
     2SELECT DISTINCT subject_name FROM tb_scores;
     3-- 结果：'语文', '数学', '英语', '物理'

  2. 拼接 SQL 字符串：利用上一步的结果，在程序中（如存储过程、Python/Java 代码）循环或拼接，生成一个完整的、包含多个 CASE WHEN 子句的 SQL 查询字符串。

    text
     1-- 步骤二：动态生成类似下面的字符串
     2"SELECT student_id, student_name, MAX(CASE WHEN subject_name = '语文' THEN score END) AS '语文', MAX(CASE WHEN subject_name = '数学' THEN score END) AS '数学', ..."

  3. 执行动态 SQL：执行这个拼接好的字符串。

多种解法

解法 1：使用 MySQL 存储过程 + 动态 SQL

这是在数据库层面解决该问题的经典方案，利用 GROUP_CONCAT 函数和 MySQL 的动态 SQL 执行能力 (PREPARE & EXECUTE)。

  • 版本：✅ MySQL 5.0+

  • 思路：

    1. 使用 GROUP_CONCAT 将所有 CASE WHEN 语句拼接成一个长字符串。
    2. 将此字符串与 SELECT ... FROM ... GROUP BY ... 的其余部分拼接成完整的 SQL 查询。
    3. 使用 PREPARE 预编译该 SQL 字符串，然后 EXECUTE 执行。

  • 可运行 SQL：

    sql
     1-- 创建存储过程
     2DELIMITER $$
     3CREATE PROCEDURE sp_dynamic_pivot()
     4BEGIN
     5    -- 1. 使用 GROUP_CONCAT 生成所有 CASE WHEN 子句
     6    -- QUOTE() 函数用于安全地包裹列名和值，防止SQL注入和特殊字符问题
     7    -- DISTINCT 确保每个科目只生成一个列
     8    SET @sql_casewhen = (
     9        SELECT GROUP_CONCAT(DISTINCT
    10            CONCAT(
    11                'MAX(CASE WHEN subject_name = ', QUOTE(subject_name),
    12                ' THEN score ELSE NULL END) AS ', QUOTE(subject_name)
    13            )
    14        )
    15        FROM tb_scores
    16    );
    17

    18    -- 2. 拼接完整的 SQL 查询字符串
    19    SET @sql_final = CONCAT(
    20        'SELECT student_id, student_name, ',
    21        @sql_casewhen,
    22        ' FROM tb_scores GROUP BY student_id, student_name'
    23    );
    24

    25    -- 3. 准备并执行动态 SQL
    26    PREPARE stmt FROM @sql_final;
    27    EXECUTE stmt;
    28    DEALLOCATE PREPARE stmt;
    29END$$
    30DELIMITER ;
    31

    32-- 调用存储过程
    33CALL sp_dynamic_pivot();

  • 时间/空间复杂度：

    • GROUP_CONCAT 步骤：O(M)O(M)，其中 M 是唯一科目的数量。
    • 最终执行的查询：O(Nlog⁡K)O(N \log K) 或 O(N+K)O(N + K)，其中 N 是总记录数，K 是学生数量。取决于 GROUP BY 的实现（排序或哈希）。

  • 索引依赖：在 (student_id, student_name, subject_name) 上建立复合索引可以极大地加速 GROUP BY 和 CASE WHEN 的数据查找。

解法 2：在应用层（客户端）拼接 SQL

在现代的软件开发实践中，将这种业务逻辑放在应用层（如 Python, Java, Go）是更常见的选择。

  • 版本：✅ 任何后端语言 + 任何 SQL 数据库

  • 思路：

    1. 应用代码执行第一次查询，获取所有唯一的科目名称。
    2. 应用代码在内存中遍历科目列表，构建 CASE WHEN 字符串。
    3. 应用代码将所有部分拼接成最终的 SQL 查询。
    4. 执行第二次查询，获取最终结果。

  • Python 示例代码：

    python
     1import mysql.connector
     2

     3# 假设 db_cursor 是一个数据库游标
     4

     5# 1. 获取所有唯一的科目
     6db_cursor.execute("SELECT DISTINCT subject_name FROM tb_scores")
     7subjects = [row[0] for row in db_cursor.fetchall()]
     8

     9# 2. 在应用层构建 CASE WHEN 子句
    10case_when_clauses = []
    11for subject in subjects:
    12    # 注意：在真实代码中，对 subject 名称要进行严格的清理和转义，防止SQL注入
    13    # 这里的 f-string 仅为演示，生产环境应使用更安全的方式
    14    safe_subject_alias = f"`{subject.replace('`', '``')}`" # 简单的转义
    15    safe_subject_value = f"'{subject.replace('\'', '\\\'')}'" # 简单的转义
    16    clause = f"MAX(CASE WHEN subject_name = {safe_subject_value} THEN score END) AS {safe_subject_alias}"
    17    case_when_clauses.append(clause)
    18

    19# 3. 拼接最终 SQL
    20final_sql = f"""
    21    SELECT
    22        student_id,
    23        student_name,
    24        {', '.join(case_when_clauses)}
    25    FROM
    26        tb_scores
    27    GROUP BY
    28        student_id, student_name
    29"""
    30

    31# 4. 执行最终查询
    32print("Generated SQL:\n", final_sql)
    33db_cursor.execute(final_sql)
    34results = db_cursor.fetchall()
    35# for row in results:
    36#     print(row)

  • 复杂度与索引：与解法 1 相同，因为最终执行到数据库的 SQL 逻辑是一致的。

  • 何时使用：当业务逻辑复杂、需要与应用其他部分紧密集成、或希望数据库保持“无逻辑”状态时，此方法更优。它将逻辑与数据分离，更易于测试、维护和扩展。

常见低分回答

  1. 尝试用一条静态 SQL 解决：候选人可能写出类似 SELECT ... PIVOT ... 的语法，但这在 MySQL 中不原生支持（SQL Server/Oracle 支持），且即便支持，IN (...) 部分通常也需要动态生成。这表明候选人对 SQL 标准和方言不熟悉。
  2. 拼接 SQL 时不考虑 SQL 注入：直接使用字符串拼接 CONCAT('...', subject_name, '...')，如果 subject_name 包含恶意代码（如 ' OR 1=1; --），将导致严重的安全漏洞。高分回答必须提及 QUOTE() 函数或在应用层进行严格的转义和白名单校验。
  3. 忽略 GROUP_CONCAT 的长度限制：MySQL 的 group_concat_max_len 变量默认值通常较小（如 1024 字节）。如果科目非常多，生成的 CASE WHEN 字符串会被截断，导致语法错误。面试者应指出这个潜在问题并知道如何修改它 (SET SESSION group_concat_max_len = ...)。
  4. 混淆聚合函数的作用：在 CASE WHEN 外层不使用 MAX/SUM/AVG 等聚合函数，这将导致 GROUP BY 语法错误，因为 SELECT 的非聚合列必须出现在 GROUP BY 子句中。
  5. 对 NULL 值的处理不清晰：CASE WHEN 的 ELSE 分支默认为 NULL，这是正确的。但如果面试官追问“如何显示为 0”，候选人应能答出使用 COALESCE(MAX(...), 0)。

工程实践与优化

  • 性能瓶颈：当科目数量非常多（成百上千）时，生成的 SQL 语句会非常长，数据库解析和生成执行计划的成本会显著增加。同时，返回的结果集也非常宽，占用大量网络带宽和客户端内存。
  • 索引是关键：对于 tb_scores 表，GROUP BY student_id, student_name 和 WHERE subject_name = ... 的过滤是核心操作。因此，一个 (student_id, student_name, subject_name) 的复合索引能极大地提升性能，理想情况下可以实现 Index-only Scan 或 Using index for group-by。
  • 查看执行计划：使用 EXPLAIN 分析最终生成的动态 SQL。
    • type 字段：理想情况是 range 或 index，最差是 ALL（全表扫描）。
    • key 字段：确认是否用上了我们创建的复合索引。
    • Extra 字段：如果出现 Using temporary; Using filesort;，说明 GROUP BY 无法利用索引，性能会很差。Using index for group-by 是最佳情况。
  • 替代方案：在很多场景下，强行在关系型数据库中进行动态行转列并非最佳实践。
    • 前端/BI 工具处理：将原始的“窄表”数据返回给前端或 BI 工具（如 Tableau, Power BI），它们内置了强大的数据透视功能，性能好且交互灵活。
    • 使用 OLAP 数据库：对于大规模数据分析场景，使用 ClickHouse、Druid 等列式存储的 OLAP 数据库，它们对这类聚合查询有极致的性能优化。
    • 数据建模：如果业务场景固定需要宽表，可以考虑在数据仓库的 ETL 流程中，通过离线任务（如 Spark/Flink）提前将数据处理成宽表，供下游查询。

常见追问与延伸题目

  1. 追问：如果这张表有十亿条记录，几千个科目，你的存储过程方案会有什么问题？怎么优化？

    • 回答要点 1 (问题)：group_concat_max_len 限制将是首要问题，即使调大，生成的 SQL 字符串也可能达到 MySQL 协议包大小的上限 (max_allowed_packet)。数据库解析超长 SQL 的开销巨大。查询本身也会因大量 CASE WHEN 扫描全表多次而极慢。
    • 回答要点 2 (优化)：此时必须放弃实时动态 SQL。应采用预计算的策略。创建一个离线的 ETL 作业（如每日执行的 Spark/Flink 任务），读取原始数据，进行分布式计算完成行转列，并将结果写入一张新的“宽”结果表中。线上查询直接查这张预计算好的宽表，实现毫秒级响应。这体现了从“在线分析处理 (OLAP)”到“批处理”的思想转变。

  2. 追问：反过来，如何将一个宽表（列是科目）动态地转换为窄表（行是科目）？也就是“列转行”（Unpivot）。

    • 回答要点 1 (MySQL 传统方案)：使用 UNION ALL。为每个科目列写一个 SELECT 语句，然后用 UNION ALL 连接起来。这种方法冗长且性能不佳，因为每个 SELECT 都可能扫描一次表。
      sql
       1SELECT student_id, student_name, '语文' AS subject_name, `语文` AS score FROM pivot_table
       2UNION ALL
       3SELECT student_id, student_name, '数学' AS subject_name, `数学` AS score FROM pivot_table
       4...
    • 回答要点 2 (MySQL 8.0+ / PostgreSQL 方案)：可以使用 VALUES 或 JSON 函数结合 LATERAL JOIN，这比 UNION ALL 更高效、更优雅。例如，可以先将一行数据转成 JSON 对象，再利用 JSON_TABLE 函数将其展开成多行。
    • 回答要点 3 (其他数据库)：SQL Server 和 Oracle 提供了原生的 UNPIVOT 关键字，语法简洁，性能也更好。

  3. 追问：如果要转换的列不是数值类型，而是字符串（比如每个科目的授课老师），MAX() 函数还适用吗？

    • 回答要点：适用，但需要理解其行为。MAX() 和 MIN() 同样适用于字符串类型，它们会按照字典序进行比较。因为 GROUP BY 后每个分组只有一个非 NULL 值，所以 MAX() 或 MIN() 都能准确地取到那个唯一的字符串值。如果一个学生一个科目可能对应多个老师（数据本身需要聚合），则应使用 GROUP_CONCAT() 来将所有老师姓名拼接成一个字符串，如 GROUP_CONCAT(teacher_name)。这考察了对聚合函数细节的掌握。
← Previous
把宽表的多个分数列转换成长表（学号、科目、分数）。(列转行)
Next →
把一列以逗号分隔的字符串拆成多行。(MySQL 8 / PostgreSQL string_to_array)