SQL 数据透视

学习PIVOT · 难度：高级 · +15XP

SQL 数据透视（PIVOT）

数据透视是将行数据转换为列的技术。设想一个场景：成绩表中每个学生的各科成绩分别占一行（长格式），而你希望每个学生占一行，各科成绩分布在不同的列中（宽格式）。这就是数据透视的核心任务。虽然部分数据库提供了 PIVOT 关键字，但使用 CASE WHEN + GROUP BY 的通用方案可以在所有数据库中实现数据透视，是每个 SQL 开发者都应掌握的基本功。

CASE WHEN 数据透视原理

核心思路：用 CASE WHEN 将行值条件性地分配到不同列，然后用 GROUP BY 和聚合函数压缩为一行：

SELECT
    分组列,
    MAX(CASE WHEN 条件1 THEN 值 END) AS 列1,
    MAX(CASE WHEN 条件2 THEN 值 END) AS 列2
FROM 表名
GROUP BY 分组列;

完整示例：成绩表行转列

原始表（长格式）：

-- scores: name | subject | score
-- 张三   | 数学   | 90
-- 张三   | 英语   | 85
-- 李四   | 数学   | 78
-- 李四   | 英语   | 92

透视后（宽格式）：

SELECT
    name,
    MAX(CASE WHEN subject = '数学' THEN score END) AS math,
    MAX(CASE WHEN subject = '英语' THEN score END) AS english,
    MAX(CASE WHEN subject = '语文' THEN score END) AS chinese
FROM scores
GROUP BY name;

结果：每人一行，各科成绩分布在 math、english、chinese 三列中。

常见的透视统计场景

-- 按年龄段统计及格人数和总人数
SELECT
    age,
    SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS failed,
    COUNT(*) AS total
FROM students
GROUP BY age;
-- 按月份汇总不同状态的订单数
SELECT
    strftime('%Y-%m', order_date) AS month,
    SUM(CASE WHEN status = '已完成' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN status = '已取消' THEN 1 ELSE 0 END) AS cancelled,
    SUM(CASE WHEN status = '待处理' THEN 1 ELSE 0 END) AS pending
FROM orders
GROUP BY month;

CASE WHEN 实现行转列步骤

1. 确定分组列（透视后的主键，如 name）
2. 确定条件列（判断依据，如 subject）
3. 确定值列（要显示在交叉格中的数据，如 score）
4. 对每个目标列写一个 CASE WHEN，用 MAX 或 SUM 包裹
5. 按分组列 GROUP BY

数据库专用 PIVOT 语法

-- SQL Server
SELECT name, [数学], [英语], [语文]
FROM scores
PIVOT (MAX(score) FOR subject IN ([数学], [英语], [语文])) AS pvt;
-- PostgreSQL (使用 tablefunc 扩展)
SELECT * FROM crosstab('SELECT name, subject, score FROM scores ORDER BY 1,2')
AS ct(name TEXT, math INT, english INT, chinese INT);

实战任务

1. 将成绩表的长格式转换为宽格式（每人一行，各科一列）
2. 按年龄段统计及格和不及格人数
3. 按月份汇总不同状态的订单数量