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

把成绩表（学号、科目、分数）转换成宽表（学号、语文、数学、英语）。(行转列)

Pivot the scores table (Student ID, Subject, Score) to a wide table (Student ID,

答案语言

考察要点

这道题是 SQL 面试中考察数据处理和分析能力的经典题目，面试官想看你是否掌握以下两点：

数据重塑（Data Reshaping）能力：能否将数据从“长格式”（Long Format）转换为“宽格式”（Wide Format），这是数据ETL和报表制作中的核心操作。
条件聚合（Conditional Aggregation）的熟练运用：是否能灵活使用 CASE WHEN 表达式与 GROUP BY 及聚合函数（如 MAX, SUM）相结合，这是实现数据透视（Pivot）的标准 SQL 方法。

核心概念

本题的核心是实现数据透视（Pivot），即将原本存储在多行中的不同类别的数据，转换到单行的多个列中。在标准 SQL（尤其是 MySQL）中，这通常通过“条件聚合”来实现。

核心思想：GROUP BY + 聚合函数 + CASE WHEN

GROUP BY student_id：将所有属于同一个学生（student_id）的行聚合在一起，为每个学生生成一个最终的输出行。
CASE WHEN subject = '...' THEN score END：这是一个条件表达式。在每个学生的分组内，它会遍历该学生的所有成绩记录。当科目匹配时（如 subject = '语文'），它返回该科的分数 score；如果不匹配，它默认返回 NULL。
MAX(...) 或 SUM(...)：将 CASE WHEN 的结果进行聚合。由于每个学生每个科目只有一条记录，所以在分组内，MAX(CASE WHEN subject = '语文' ...) 的输入会是 {85, NULL, NULL} 这样的集合，MAX 函数会提取出唯一的非 NULL 值 85。使用 SUM 也能达到同样效果，因为 SUM({85, NULL, NULL}) 的结果也是 85。MAX 在语义上更清晰，表示“取那个值”。

数据示例

假设原始成绩表 scores 如下：

student_id	subject	score
1	语文	85
1	数学	92
1	英语	88
2	语文	78
2	数学	95
2	英语	89

经过行转列操作后，期望的输出宽表 scores_wide 如下：

student_id	chinese	math	english
1	85	92	88
2	78	95	89

多种解法

解法 1：使用 `CASE WHEN` 结合 `GROUP BY`（最通用、最高效）

这是实现行转列的最标准、最通用的方法，在所有主流关系型数据库中都受支持。

版本兼容性：✅ MySQL (所有版本), PostgreSQL, Oracle, SQL Server 等。

SQL 实现：

sql

 1SELECT
 2    student_id,
 3    MAX(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese,
 4    MAX(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math,
 5    MAX(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS english
 6FROM
 7    scores
 8GROUP BY
 9    student_id;

复杂度分析：
- 时间复杂度：通常为 $O(N)$ 或 $O(N \log N)$ 。如果数据库使用哈希聚合（Hash Aggregation），复杂度为 $O(N)$ ；如果需要排序（Sort-based Aggregation），则为 $O(N \log N)$ ，其中 N 是表中的总行数。
- 空间复杂度： $O(K)$ ，其中 K 是 student_id 的基数（唯一学生数），用于在内存中构建哈希表或排序中间结果。
索引依赖：
- 为了优化 GROUP BY，可以在 student_id 列上创建索引。
- 最佳索引是 覆盖索引 (student_id, subject, score)。这样，数据库引擎只需扫描索引即可获取所有需要的数据，无需回表查询主键，性能最高。

解法 2：使用多次 `LEFT JOIN`（思路清晰，但性能通常较差）

通过将每个科目筛选为一个子查询，然后将这些子查询基于 student_id 连接起来。

版本兼容性：✅ 所有支持 JOIN 的数据库。

SQL 实现：

sql

 1SELECT
 2    s_base.student_id,
 3    s_chinese.score AS chinese,
 4    s_math.score AS math,
 5    s_english.score AS english
 6FROM
 7    (SELECT DISTINCT student_id FROM scores) AS s_base
 8LEFT JOIN
 9    (SELECT student_id, score FROM scores WHERE subject = '语文') AS s_chinese
10    ON s_base.student_id = s_chinese.student_id
11LEFT JOIN
12    (SELECT student_id, score FROM scores WHERE subject = '数学') AS s_math
13    ON s_base.student_id = s_math.student_id
14LEFT JOIN
15    (SELECT student_id, score FROM scores WHERE subject = '英语') AS s_english
16    ON s_base.student_id = s_english.student_id;

复杂度分析：
- 时间复杂度：较高。此查询需要对 scores 表进行多次扫描（或索引扫描），每次扫描筛选一个科目。如果 scores 表很大，这将导致显著的 I/O 开销。Join 操作的复杂度也取决于 student_id 上的索引情况。
- 空间复杂度：需要存储每个子查询的中间结果集，空间开销较大。
索引依赖：
- student_id 列上的索引对于 JOIN 的性能至关重要。
- (subject, student_id) 上的复合索引可以极大地加速每个子查询的 WHERE 过滤。

何时使用：此方法逻辑上更符合“将不同部分拼起来”的直觉，但性能通常不如解法 1。在 scores 表非常小，或者科目已经预先分到不同表中时，可以考虑。在面试中，可以作为一种备选方案提出，以展示你解决问题的多维思路，但要明确指出其性能局限性。

常见低分回答

忘记使用聚合函数：SELECT student_id, CASE WHEN subject = '语文' THEN score END AS chinese FROM scores GROUP BY student_id;。这是无效的 SQL，因为 CASE 语句的结果列既不在 GROUP BY 子句中，也没有被聚合函数包裹。
忘记 GROUP BY 子句：SELECT student_id, MAX(CASE WHEN subject = '语文' THEN score END) AS chinese FROM scores;。如果没有 GROUP BY，聚合函数将作用于整个表，只返回一行结果，这是错误的。
错误地使用 WHERE 子句：SELECT student_id, score FROM scores WHERE subject = '语文' AND subject = '数学';。这种逻辑试图在一行中同时满足两个互斥的条件，永远不会返回任何结果，完全误解了题目意图。
对 NULL 处理不当：虽然在本题中 MAX 能很好地处理 NULL，但有些候选人可能会写出复杂的 IFNULL 或 COALESCE 结构，这不仅冗余，还可能在某些聚合函数（如 COUNT）中引入错误。
滥用自连接：写出类似 SELECT ... FROM scores s1 JOIN scores s2 ON s1.student_id = s2.student_id WHERE s1.subject='语文' AND s2.subject='数学' 的查询。这种方式不仅复杂，而且每增加一个科目就需要多一次 JOIN，扩展性极差，性能也很糟糕。

工程实践与优化

动态行转列（Dynamic Pivot）：在真实业务中，科目的种类可能不是固定的。这时无法硬编码 CASE WHEN 语句。需要使用动态 SQL 来解决。
1. 查询列名：先从 scores 表中查询出所有不重复的科目名称。
  sql
```
 1-- 在 MySQL 中使用 GROUP_CONCAT 构造 CASE 语句
 2SELECT
 3  GROUP_CONCAT(DISTINCT
 4    CONCAT(
 5      'MAX(CASE WHEN subject = ''',
 6      subject,
 7      ''' THEN score END) AS `',
 8      subject, '`'
 9    )
10  )
11INTO @sql_pivot
12FROM scores;
```
2. 拼接 SQL 字符串：在应用层（如 Java, Python）或数据库存储过程中，将上一步查出的列名拼接成一个完整的 SELECT 查询字符串。
3. 执行动态 SQL：使用 PREPARE 和 EXECUTE 语句来执行这个动态生成的 SQL。
  sql
```
 1SET @sql = CONCAT('SELECT student_id, ', @sql_pivot, ' FROM scores GROUP BY student_id');
 2PREPARE stmt FROM @sql;
 3EXECUTE stmt;
 4DEALLOCATE PREPARE stmt;
```
性能考量：当数据量达到千万或上亿级别时，GROUP BY 的性能至关重要。
- 执行计划分析：使用 EXPLAIN 查看执行计划。理想情况下，Extra 字段应显示 Using index，表示用到了覆盖索引。避免出现 Using temporary; Using filesort，这表示 MySQL 无法在内存中完成聚合，需要使用临时表和磁盘排序，性能会急剧下降。
- 索引优化：如前所述，INDEX(student_id, subject, score) 是本场景下的最佳索引。student_id 在前，可以优化 GROUP BY；subject 和 score 在后，使得查询所需的所有列都包含在索引中，实现覆盖索引扫描。

常见追问与延伸题目

追问：如果反过来，要把宽表（学号、语文、数学、英语）转成窄表（学号、科目、分数），该怎么做？（列转行/Unpivot）
- 回答要点：这叫“逆透视”或“列转行”（Unpivot）。最通用的方法是使用 UNION ALL。
  sql
```
 1SELECT student_id, '语文' AS subject, chinese AS score FROM scores_wide WHERE chinese IS NOT NULL
 2UNION ALL
 3SELECT student_id, '数学' AS subject, math AS score FROM scores_wide WHERE math IS NOT NULL
 4UNION ALL
 5SELECT student_id, '英语' AS subject, english AS score FROM scores_wide WHERE english IS NOT NULL;
```
  - 要点：使用 UNION ALL 而不是 UNION，因为 UNION 会进行去重，带来不必要的性能开销，而这里各子查询结果不可能重复。
  - 补充：在 Oracle 或 SQL Server 中，可以使用专门的 UNPIVOT 语法，更简洁。
追问：如果数据量巨大，达到 10 亿级别，你刚才写的 GROUP BY 查询会有什么问题？如何优化？
- 回答要点 1 (分布式计算)：单机 MySQL 很难处理 10 亿级别的 GROUP BY。内存会成为瓶颈，导致大量的磁盘 I/O。这时需要考虑分布式计算框架，如 Spark SQL, Hive, Flink。它们会将数据分片（Partition），在多个节点上并行执行 GROUP BY，然后将各节点的结果合并，这是解决超大规模数据聚合的根本方法。
- 回答要点 2 (预聚合)：如果这是一个常见的报表需求，可以创建一张预聚合的“物化视图”或汇总表。例如，每天定时任务将当天的增量数据进行聚合，然后更新到最终的宽表中。这样，在线查询时直接查这张汇总表，速度极快，避免了实时进行大规模聚合。
- 回答要点 3 (数据库选型)：可以考虑使用列式存储数据库（如 ClickHouse）或支持大规模并行处理（MPP）的数据库（如 Greenplum, TiDB）。这类数据库为分析型查询（OLAP）设计，其列存特性和并行计算能力对 GROUP BY 操作有极好的性能表现。
追问：在其他数据库比如 PostgreSQL 或 SQL Server 中，有更方便的写法吗？
- 回答要点：是的，一些数据库提供了专门的 PIVOT 语法糖。
  - SQL Server / Oracle: 提供了 PIVOT 关键字，语法更具可读性。
    sql
```
 1-- SQL Server 示例
 2SELECT student_id, [语文], [数学], [英语]
 3FROM scores
 4PIVOT (
 5    MAX(score)
 6    FOR subject IN ([语文], [数学], [英语])
 7) AS PivotTable;
```
  - PostgreSQL: 没有内建的 PIVOT 语法，但可以通过 crosstab 函数（需要安装 tablefunc 扩展）来实现，功能更强大，但使用也更复杂。不过，CASE WHEN 的方法在 PostgreSQL 中同样高效且常用。
  - 总结：虽然有语法糖，但 CASE WHEN + GROUP BY 的方法是 SQL 的“最大公约数”，通用性最好，也是面试时必须掌握的核心方案。