SQL中的行转列和列转行

SQL中的行转列和列转行是数据重构的常见操作，分别通过条件聚合（如SUM+IF）和纵向拼接（如UNION）实现。

行转列的核心思路是将多行数据按条件聚合为单行，同时将单列数据拆分为多列。典型场景是将学生成绩的长表（每行记录一门课程成绩）转换为宽表（每行包含所有课程成绩）。

• 实现方法：使用SUM+IF组合进行条件聚合。

  原理：

  通过IF函数筛选特定课程成绩，其他课程成绩设为NULL。

  使用聚合函数（如SUM）提取非空值，确保每门课程成绩独立成列。

  按用户ID（uid）分组，实现多行变一行。

  示例SQL：

  SELECT uid, sum(if(course='语文', score, NULL)) as `语文`, sum(if(course='数学', score, NULL)) as `数学`, sum(if(course='英语', score, NULL)) as `英语`, sum(if(course='物理', score, NULL)) as `物理`, sum(if(course='化学', score, NULL)) as `化学`FROM scoreLongGROUP BY uid

  关键点：

  IF(course='语文', score, NULL)：仅当课程为语文时返回成绩，否则返回NULL。

  聚合函数（如SUM）会忽略NULL值，最终得到每门课程的独立成绩。

  列名需与课程名称一致，便于结果可读性。

列转行的核心思路是将单行多列数据拆分为多行单列，同时保留关联信息。典型场景是将学生成绩的宽表（每行包含所有课程成绩）转换为长表（每行记录一门课程成绩）。

• 实现方法：使用UNION纵向拼接多张衍生表。

  原理：

  针对每门课程生成一张衍生表，包含用户ID、课程名称和成绩。

  使用UNION合并所有衍生表，备搏衡实现多列变单列。

  通过WHERE过滤空值，避免无效记录。

  示例SQL：

  SELECT uid, '语文' as course, `语文` as scoreFROM scoreWideWHERE `语文` IS NOT NULLUNIONSELECT uid, '数学' as course, `数学` as scoreFROM scoreWideWHERE `数学` IS NOT NULLUNIONSELECT uid, '英语' as course, `英语` as scoreFROM scoreWideWHERE `英语` IS NOT NULLUNIONSELECT uid, '物理' as course, `物理` as scoreFROM scoreWideWHERE `物理` IS NOT NULLUNIONSELECT uid, '化学' as course, `化学` as scoreFROM scoreWideWHERE `化学` IS NOT NULL

  关键点：

  字符串常量：课程名称（如'语文'）用单引号包裹，表示固定值。

  列引用：成绩字段（如`语文`）用反引号包裹，表示从宽表中提取的列。

  空值过滤：WHERE条件确保仅保留有效成绩记录。

  UNION与UNION ALL：

  UNION：自动去重，但性能较低。

  UNION ALL：保留所有记录，性能更高（适用于无重复或无需去重的场景）。

• 行转列：

  优点：逻辑清晰，适合固定列数的场景。

  缺点：需手动指定每列条件，扩展性差仿做（如新增课程需修改SQL）。

  优化：动态SQL或存储过程可解决扩展性问题。

• 列转行：

  优点：直观易懂，适合少量列的转换。

  缺点：需为每列生成独立查询，代码冗长。

  优化：使用CROSS JOIN与条件判断结合，减少重复代码。

• 行转列：通过条件聚合将多行数据压缩为单行，核心是IF或CASE WHEN与聚合函数的结合。
• 列转行：通过纵向拼接将单银姿行多列数据展开为多行，核心是UNION与字符串常量的配合。
• 选择依据：根据数据形态和业务需求选择合适方法，行转列适合分析报表生成，列转行适合数据规范化处理。