MySQL 中的数据透视表

行转列常用于生成汇总或报表。MySQL 没有内置的 PIVOT 函数，但可以通过条件聚合，使用 IF 或 CASE 创建固定列。输出列集合会变化时，动态 SQL 可以根据数据构建查询。示例使用学生成绩：按学生分组后，每个姓名对应一行，每门科目对应一列。

准备示例数据

示例中，Sam 的科目 1 有两条成绩记录。透视结果应取较高的成绩 99。Martin 没有科目 4 的记录，因此固定列示例会在该位置显示 NULL。下方展示的输入图包含本例所用的相同记录。

输入记录中，Sam 的科目 1 有两条成绩，因此透视结果应显示较高的分数 99。

运行此示例：

本节中的代码以及显示的标签或输出仍为英文。

rows = [
    ("Sam", 1, 70), ("Sam", 2, 77), ("Sam", 3, 71), ("Sam", 4, 70),
    ("Sam", 1, 99), ("John", 1, 89), ("John", 2, 87),
    ("John", 3, 88), ("John", 4, 89), ("Martin", 1, 60),
    ("Martin", 2, 47), ("Martin", 3, 68),
]
subjects = (1, 2, 3, 4)
print("name|" + "|".join(f"Sub{s}" for s in subjects))
for name in sorted({row[0] for row in rows}):
    marks = [max((score for student, subject, score in rows
                  if student == name and subject == s), default="NULL")
             for s in subjects]
    print(name + "|" + "|".join(str(score) for score in marks))

name|Sub1|Sub2|Sub3|Sub4
John|89|87|88|89
Martin|60|47|68|NULL
Sam|99|77|71|70

此 Python 示例直观展示了分组和取最高成绩的行为；请在自己的表上运行下方的 MySQL 查询。建表语句为 CREATE TABLE student (name VARCHAR(20), subjectid INT, marks INT);。请插入图中所示的记录，包括 Sam 的两条科目 1 记录，然后使用条件聚合生成报表。由于代码块按原样保留，其中的标签和输出仍为英文。

使用 IF 和 MAX 进行透视

为每个科目列编写一个条件表达式，然后按 name 分组，使每位学生只返回一行：SELECT name, MAX(IF(subjectid=1, marks, NULL)) AS Sub1, MAX(IF(subjectid=2, marks, NULL)) AS Sub2, MAX(IF(subjectid=3, marks, NULL)) AS Sub3, MAX(IF(subjectid=4, marks, NULL)) AS Sub4 FROM student GROUP BY name;。IF 在行符合条件时返回成绩，否则返回 NULL。MAX 会汇总每个姓名对应的各科目列值。如果同一学生和科目有多条匹配记录，MAX 会返回其中最高的成绩；它不会只选第一条记录，也不会限制匹配记录的数量。

固定列结果显示 Sam 的科目 1 最高成绩为 99；Martin 没有科目 4 的记录，因此该位置为 NULL。

图中展示了查询结果，包括 Sam 的科目 1 成绩 99，以及 Martin 缺少科目 4 记录时的 NULL。这些图片展示的是结果，并非数据库客户端截图。此处的 SQL 和结果均可选中复制。

使用 CASE 进行透视

CASE 可以更明确地表达相同条件。此查询与 IF 版本会生成相同的固定列结果：SELECT name, MAX(CASE WHEN subjectid=1 THEN marks END) AS Sub1, MAX(CASE WHEN subjectid=2 THEN marks END) AS Sub2, MAX(CASE WHEN subjectid=3 THEN marks END) AS Sub3, MAX(CASE WHEN subjectid=4 THEN marks END) AS Sub4 FROM student GROUP BY name;。没有 ELSE 时，不匹配的行会返回 NULL；MAX 会忽略这些不匹配的值。因此，某个学生没有对应科目记录时，该列仍为 NULL。重复匹配记录也按同样的取最大值规则处理。

生成动态透视列

IF 和 CASE 查询的输出列是固定的：编写查询时必须知道科目值，并为每一列添加一个表达式。动态 SQL 可以根据不同的科目 ID 生成这些表达式。GROUP_CONCAT 用于拼接列表达式，再由预处理语句执行生成的查询：

SET SESSION group_concat_max_len = 100000; SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END) AS \’, subjectid, ‘`’) ORDER BY subjectid SEPARATOR ‘, ‘) INTO @columns FROM student; SET @sql = CONCAT(‘SELECT name, ‘, @columns, ’ FROM student GROUP BY name’); SELECT @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;`

生成的查询会为表中每个科目创建一个输出列。在这个示例中，成绩均为非负数，因此 ELSE 0 会让缺少的科目显示为 0，与动态结果图一致。只有在成绩不可能为负数时，这种写法才合适：否则，不匹配行返回的 0 可能会在 MAX 运算中大于匹配到的负成绩。如果成绩可能为负数，请改用 ELSE NULL；这样 MAX 会忽略不匹配的行，而没有对应科目记录的学生在该列会得到 NULL。准备语句前请检查 @sql，尤其要注意值或标识符可能来自不可信输入的情况。生成的语句和预处理语句都属于当前会话，因此必须在同一个 MySQL 连接中运行这些语句。GROUP_CONCAT 的最大长度可以配置；如果生成的表达式列表超过 group_concat_max_len，内容可能会被截断。请设置合适的会话值，并在执行前检查生成的 SQL。

生成的结果会为每个科目 ID 添加一列；在示例成绩均为非负数这一前提下，Martin 缺少科目 4 的记录，因此该列显示为 0。

这种方法会根据数据中的科目 ID 调整列清单。不过，结果结构仍会在预处理语句时确定；如果科目数量很多或经常变化，使用应用程序代码或报表工具可能更方便。