把宽表的多个分数列转换成长表（学号、科目、分数）。(列转行)

考察要点

这道题面试官想看你是否掌握了 SQL 中处理数据结构转换的能力，特别是**列转行（Unpivot）**的技巧。它考察你是否能灵活运用基础 SQL 命令（如 UNION ALL）或更高级的集合操作（如 JOIN）来重塑数据，同时也检验你对不同解法性能和维护性差异的理解。

核心概念

本题的核心是将宽表 (Wide Table) 转换为长表 (Long Table)。

• 宽表：将同一实体的不同属性存储在不同的列中。这种设计直观，但扩展性差（增加新科目需要修改表结构）。
• 长表：将属性本身作为一列的值。这种设计符合数据库范式，扩展性好（增加新科目只需插入新行）。

数据示例：

假设原始宽表 scores 如下：

转换后的目标长表应如下：

实现这一转换的关键 SQL 概念包括：

1. UNION ALL：

   • 作用：将多个 SELECT 语句的结果集垂直堆叠在一起。
   • 关键点：与 UNION 不同，UNION ALL 不会进行去重操作，因此性能更高。在本题场景中，每行数据（学号、科目、分数）天然是唯一的，无需去重，故应使用 UNION ALL。

2. CROSS JOIN + CASE WHEN：

   • 作用：通过 CROSS JOIN 生成一个包含所有学生和所有科目的笛卡尔积，然后使用 CASE WHEN 表达式为每一行匹配正确的分数。
   • 思路：这是一种更偏向“集合论”的思维方式，将一次全表扫描的结果与一个固定的科目集合进行关联计算。

多种解法

解法 1：使用 UNION ALL

这是最直观、最通用的方法，兼容所有版本的 SQL 数据库。思路是为每个科目列编写一个 SELECT 语句，然后将它们合并。

• 版本兼容性: ✅ 所有 SQL 数据库（MySQL, PostgreSQL, SQL Server, Oracle 等）
• SQL 代码:
  sql

  ⧉⛶

   1SELECT
   2    student_id,
   3    '语文' AS subject,
   4    chinese_score AS score
   5FROM scores
   6UNION ALL
   7SELECT
   8    student_id,
   9    '数学' AS subject,
  10    math_score AS score
  11FROM scores
  12UNION ALL
  13SELECT
  14    student_id,
  15    '英语' AS subject,
  16    english_score AS score
  17FROM scores
  18ORDER BY student_id, subject; -- 可选的排序，保证结果可读性

• 复杂度:
  • 时间复杂度：$O(K \cdot N)$，其中 $N$ 是原表行数，$K$ 是需要转换的列数。数据库需要对原表进行 $K$ 次扫描（尽管现代优化器可能优化为一次扫描，但逻辑上是多次）。
  • 空间复杂度：$O(N \cdot K)$，用于存储最终结果。
• 索引依赖: 此查询通常会导致全表扫描。由于需要读取每一行的所有分数列，即使 student_id 上有索引，也无法避免扫描整个表。

解法 2：使用 CROSS JOIN

这种方法通过一次表扫描和一次连接操作完成转换，对于列数较多的情况通常比 UNION ALL 更高效。

• 版本兼容性: ✅ 所有 SQL 数据库
• SQL 代码:
  sql

  ⧉⛶

   1SELECT
   2    s.student_id,
   3    subj.name AS subject,
   4    CASE subj.name
   5        WHEN '语文' THEN s.chinese_score
   6        WHEN '数学' THEN s.math_score
   7        WHEN '英语' THEN s.english_score
   8    END AS score
   9FROM
  10    scores AS s
  11CROSS JOIN
  12    (
  13        SELECT '语文' AS name
  14        UNION ALL SELECT '数学'
  15        UNION ALL SELECT '英语'
  16    ) AS subj
  17ORDER BY s.student_id, subj.name;

• 复杂度:
  • 时间复杂度：$O(N \cdot K)$。虽然与解法 1 相同，但它只对 scores 表进行一次全表扫描，通常实际执行效率更高。
  • 空间复杂度：$O(N \cdot K)$，用于存储 CROSS JOIN 的中间结果和最终结果。
• 索引依赖: 同样是全表扫描，不需要特定索引。

解法 3：使用 LATERAL JOIN (更现代的方案)

LATERAL JOIN 允许右侧的派生表引用左侧表的列，非常适合此类行级展开操作。语法更简洁，执行效率也高。

• 版本兼容性: ✅ MySQL 8.0+, PostgreSQL, Oracle 12c+。❌ MySQL 5.7 不支持。
• SQL 代码:
  sql

  ⧉⛶

   1-- 在 PostgreSQL 或 Oracle 中更常见的语法是 VALUES
   2SELECT
   3    s.student_id,
   4    v.subject,
   5    v.score
   6FROM
   7    scores s,
   8LATERAL (
   9    VALUES
  10        ('语文', s.chinese_score),
  11        ('数学', s.math_score),
  12        ('英语', s.english_score)
  13) AS v(subject, score)
  14ORDER BY s.student_id, v.subject;
  15
  16-- 在 MySQL 8.0.14+ 中，可以使用类似的方式
  17SELECT
  18    s.student_id,
  19    v.subject,
  20    v.score
  21FROM
  22    scores s
  23JOIN
  24    LATERAL (
  25        SELECT '语文' AS subject, s.chinese_score AS score
  26        UNION ALL
  27        SELECT '数学', s.math_score
  28        UNION ALL
  29        SELECT '英语', s.english_score
  30    ) AS v ON true
  31ORDER BY s.student_id, v.subject;

• 复杂度:
  • 时间复杂度：$O(N)$。LATERAL 子查询对 scores 表的每一行执行一次，但整个过程只需对 scores 表进行一次顺序扫描。这是目前最高效的解法之一。
  • 空间复杂度：$O(N \cdot K)$，用于存储最终结果。
• 索引依赖: 全表扫描是最高效的方式。

常见低分回答

1. 使用 UNION 而非 UNION ALL：

   • 错误描述：UNION 会对结果集进行排序和去重，带来不必要的性能开销。在此场景下，结果行天然唯一，去重是多余的。
   • 反例：SELECT ... FROM scores UNION SELECT ... FROM scores ...

2. 在客户端代码中处理：

   • 错误描述：回答“我可以用 Python/Java/Go 把数据查出来，然后循环处理成想要的格式”。这完全回避了 SQL 问题本身，表明候选人 SQL 能力不足。

3. 复杂的自连接或子查询：

   • 错误描述：设计出不必要的、难以理解的 JOIN 逻辑，而不是使用 UNION ALL 这种直接的方法。例如，尝试多次自连接 scores 表，这会使查询变得极其低效和复杂。

4. 忽略 NULL 值的处理：

   • 错误描述：虽然本题解法能自然处理 NULL（NULL 分数会保留在结果中），但有些候选人可能会在每个 SELECT 子句中加上 WHERE xxx_score IS NOT NULL。这会改变题目要求，从“转换”变成了“转换并过滤”，除非面试官明确要求，否则属于画蛇添足。

5. 忘记给常量列起别名：

   • 错误描述：SELECT student_id, '语文', chinese_score FROM scores 这样的查询虽然能跑，但结果列名会是 '语文' 和 chinese_score，不符合最终 subject 和 score 的要求，显得不专业。

工程实践与优化

• 根本性优化：改进表设计。在真实工程中，频繁进行列转行操作通常意味着数据库设计存在问题。长表（student_id, subject, score）才是更规范、更具扩展性的设计。如果业务允许，应从源头改造表结构，避免每次查询都进行昂贵的转换。
• 性能考量：
  • 当待转换的列数 $K$ 较少时（如 < 10），UNION ALL 简单直观，性能也足够。
  • 当 $K$ 很大时，UNION ALL 会导致 SQL 语句过长，且可能增加数据库解析负担和扫描次数。此时 CROSS JOIN 或 LATERAL JOIN（如果支持）是更优的选择，因为它们只扫描主表一次。
• 执行计划分析 (EXPLAIN)：
  • 对于 UNION ALL 解法，EXPLAIN 会显示一个 UNION RESULT，其下有多个 SELECT，每个 SELECT 的 type 很可能是 ALL（全表扫描）。
  • 对于 CROSS JOIN 解法，EXPLAIN 会显示 scores 表的 type 为 ALL，然后与一个 DERIVED 表（即时生成的科目表）进行连接。关键在于 scores 表只被扫描了一次。
• 动态列的处理：如果科目列是动态增加的，硬编码 SQL 就不再适用。此时需要在应用层或使用数据库存储过程来动态生成 SQL。
  1. 从 INFORMATION_SCHEMA.COLUMNS 查询表结构，获取所有 _score 结尾的列名。
  2. 根据列名列表，拼接生成 UNION ALL 或 CASE WHEN 形式的 SQL 字符串。
  3. 使用 PREPARE 和 EXECUTE 语句（或等效的动态 SQL 功能）执行拼接好的查询。

常见追问与延伸题目

1. 追问：反过来怎么做？如果给你长表，如何转换成宽表？（行转列，Pivot）

   • 回答要点：使用条件聚合。通过 GROUP BY student_id 将每个学生的记录聚合为一行，然后使用 CASE WHEN 表达式在聚合函数（如 MAX 或 SUM）内部判断科目，将对应的分数放置在新的列中。
   • 示例 SQL:
     sql

     ⧉⛶

      1SELECT
      2    student_id,
      3    MAX(CASE WHEN subject = '语文' THEN score END) AS chinese_score,
      4    MAX(CASE WHEN subject = '数学' THEN score END) AS math_score,
      5    MAX(CASE WHEN subject = '英语' THEN score END) AS english_score
      6FROM
      7    long_scores_table
      8GROUP BY
      9    student_id;

2. 追问：如果科目有 100 个，手写 UNION ALL 或 CASE WHEN 太麻烦了，有什么自动化方案吗？

   • 回答要点：这在单个静态 SQL 查询中是无法实现的，因为 SQL 查询的结果集结构（列的数量和名称）必须在解析时就确定。正确的工程做法是动态 SQL。
   • 步骤：
     1. 元数据查询：编写一个查询，从系统表（如 MySQL 的 INFORMATION_SCHEMA.COLUMNS）中获取 scores 表所有需要转换的列名。
     2. SQL 字符串拼接：在应用程序（如 Python/Java）或数据库存储过程中，遍历列名列表，动态生成一个很长的 UNION ALL 或 CROSS JOIN + CASE WHEN 的 SQL 查询字符串。
     3. 执行动态 SQL：使用 PREPARE statement FROM @sql_string; EXECUTE statement; (MySQL 语法) 或其他数据库的等效功能来执行这个动态生成的 SQL。

3. 追问：如果换成其他数据库，比如 SQL Server 或 Oracle，有更简洁的写法吗？

   • 回答要点：是的，许多数据库提供了专门的 UNPIVOT 语法糖来简化这个操作，这体现了候选人的知识广度。
   • SQL Server / Oracle 示例:
     sql

     ⧉⛶

      1-- SQL Server
      2SELECT student_id, subject, score
      3FROM scores
      4UNPIVOT
      5(
      6    score FOR subject IN (chinese_score, math_score, english_score)
      7) AS unpvt;

     这种专用语法不仅更简洁、可读性更高，而且通常由数据库进行深度优化，性能也很好。