MySQL行列转化方式

好的，MySQL 中的行列转化（也称为透视和逆透视）是数据分析中非常常见的需求。它主要分为两种情况：

行转列（Pivot / Crosstab）：将表中的行数据转换为列数据。例如，将每个学生的各科成绩从多行记录变成一行多列。
列转行（Unpivot）：将表中的列数据转换为行数据。这是行转列的逆操作。

由于 MySQL 本身没有内置的 PIVOT 和 UNPIVOT 函数（这些是 SQL Server、Oracle 等数据库的语法），我们需要使用其强大的条件聚合函数（如 CASE WHEN, IF）结合聚合函数（如 SUM, MAX, MIN, AVG）来实现。

下面我们详细讲解这两种转换的方式。

1. 行转列

这是更常见、更实用的场景。我们通过一个经典的“学生成绩表”案例来说明。

原始数据与需求

假设我们有如下 student_scores 表：

student_id	student_name	subject	score
1	张三	语文	90
1	张三	数学	95
1	张三	英语	88
2	李四	语文	85
2	李四	数学	92
2	李四	英语	90
3	王五	语文	78
3	王五	数学	82
3	王五	英语	80

需求：将每个学生的各科成绩转换为列，最终展示为每个学生在同一行，各科目作为单独的列。

期望结果：

student_id	student_name	语文	数学	英语
1	张三	90	95	88
2	李四	85	92	90
3	王五	78	82	80

实现方法：使用 CASE WHEN + GROUP BY + 聚合函数

这是最核心、最通用的行转列方法。

思路：

分组（GROUP BY）：按照我们想要保留的唯一标识进行分组，这里是 student_id 和 student_name。
条件判断与赋值（CASE WHEN）：在每个分组内，使用 CASE WHEN 判断科目，然后将对应的分数“提取”出来，放到新的列中。
聚合（聚合函数）：由于我们进行了分组，而每个分组内对于新的列（如‘语文’列）只会有一个有效值（其他科目的 CASE WHEN 会返回 NULL），因此我们可以使用 MAX()、MIN() 或 SUM() 来“收集”这个值。对于一对一的行转列，MAX() 和 MIN() 效果相同。

SQL 语句：

SELECT
    student_id,
    student_name,
    MAX(CASE WHEN subject = '语文' THEN score END) AS '语文',
    MAX(CASE WHEN subject = '数学' THEN score END) AS '数学',
    MAX(CASE WHEN subject = '英语' THEN score END) AS '英语'
FROM
    student_scores
GROUP BY
    student_id, student_name;

代码解释：

CASE WHEN subject = '语文' THEN score END：当科目是‘语文’时，返回该行的分数，否则返回 NULL。
MAX(CASE WHEN ...)：在每个学生分组中，会有三行数据（对应三科）。对于‘语文’这个新列，只有一行的 CASE 表达式有分数（非 NULL），其他两行都是 NULL。MAX() 函数会忽略 NULL 值，返回那个唯一的分数值。
GROUP BY student_id, student_name：确保每个学生成为结果集中的一行。

变种：使用 IF 函数简化

IF(condition, value_if_true, value_if_false) 是 CASE WHEN 的简写形式，逻辑相同。

SELECT
    student_id,
    student_name,
    MAX(IF(subject = '语文', score, NULL)) AS '语文',
    MAX(IF(subject = '数学', score, NULL)) AS '数学',
    MAX(IF(subject = '英语', score, NULL)) AS '英语'
FROM
    student_scores
GROUP BY
    student_id, student_name;

注意：IF 函数在很多数据库（如 PostgreSQL）中不可用，但在 MySQL 中非常方便。

处理多行数据（聚合场景）

如果原始数据中一个学生有多条同一科目的记录（例如，多次考试），我们需要在行转列前进行聚合（比如求平均分或总分）。

假设 student_scores 表增加了一次考试记录：

student_id	student_name	subject	score
1	张三	语文	90
1	张三	语文	92
1	张三	数学	95

现在我们想计算每个学生的平均分并转为列。

SQL 语句：

SELECT
    student_id,
    student_name,
    AVG(CASE WHEN subject = '语文' THEN score END) AS '语文_avg',
    AVG(CASE WHEN subject = '数学' THEN score END) AS '数学_avg'
    -- 可以在这里继续使用 MAX, MIN, SUM 等
FROM
    student_scores
GROUP BY
    student_id, student_name;

2. 列转行

列转行是将宽表结构变为窄表结构。MySQL 提供了专门的 UNION ALL 操作符来高效实现这一点。

原始数据与需求

假设我们有上面行转列后的结果表 student_scores_pivot（或任何类似的宽表）：

student_id	student_name	语文	数学	英语
1	张三	90	95	88
2	李四	85	92	90

需求：将其转换回最初的“长格式”表结构。

期望结果：

student_id	student_name	subject	score
1	张三	语文	90
1	张三	数学	95
1	张三	英语	88
2	李四	语文	85
2	李四	数学	92
2	李四	英语	90

实现方法：使用 UNION ALL

思路：将每个需要转换的列都通过 SELECT ... UNION ALL SELECT ... 拼接起来。每个 SELECT 语句从原表中选取相同的公共列（如 student_id, student_name），并为新列（subject, score）赋予固定的值和对应列的值。

SQL 语句：

SELECT
    student_id,
    student_name,
    '语文' AS subject,
    `语文` AS score
FROM
    student_scores_pivot
UNION ALL
SELECT
    student_id,
    student_name,
    '数学' AS subject,
    `数学` AS score
FROM
    student_scores_pivot
UNION ALL
SELECT
    student_id,
    student_name,
    '英语' AS subject,
    `英语` AS score
FROM
    student_scores_pivot
ORDER BY
    student_id, subject; -- 为了结果有序，最好加上排序

代码解释：

第一个 SELECT 从原表中取出所有行，并将 语文 列重命名为 score，同时硬编码 '语文' 作为 subject 列的值。
UNION ALL 将三个 SELECT 的结果垂直堆叠在一起。
注意：必须使用 UNION ALL 而不是 UNION。UNION 会对结果集进行去重，带来不必要的性能开销。UNION ALL 只是简单合并，效率更高。因为我们明确知道每一部分的数据都是唯一的，不会有重复行。
如果列名包含特殊字符或是关键字（如这里的语文），需要用反引号 ` 括起来。

总结与注意事项

转换类型	核心方法	关键点
行转列	`CASE WHEN` / `IF` + `GROUP BY` + 聚合函数	1. 分组字段是结果的唯一标识。 2. 聚合函数（如 `MAX`）用于处理分组内的多值，对一对一转换，`MAX`/`MIN`/`SUM` 均可。 3. 适用于报表生成、数据透视。
列转行	`UNION ALL`	1. 每个要转换的列对应一个 `SELECT` 语句。 2. 使用 `UNION ALL` 以提升性能。 3. 公共列必须在每个 `SELECT` 中都出现。 4. 适用于数据规范化、适配某些分析工具。

注意事项：

动态行列转换：上述行转列方法是静态的，即需要提前知道有多少列（科目）。如果需要动态转换（科目不固定），在 MySQL 中实现起来非常复杂，通常需要借助存储过程或应用程序代码来动态生成 SQL 语句，这超出了普通 SQL 查询的范畴。
性能：行转列时，如果数据量巨大且分组字段选择性不强，需要注意性能。列转行的 UNION ALL 在数据量翻倍后会显著影响性能。
聚合函数的选择：在行转列时，根据业务需求选择合适的聚合函数。求平均用 AVG，求和用 SUM，取最大/最小用 MAX/MIN。如果确定是一对一关系，它们的结果是一样的。