行列转换
问题
SQL 中如何实现行转列（PIVOT）和列转行（UNPIVOT）？
答案
行列转换是数据分析和报表中的常见需求。比如把"每行一条销售记录"转换为"每列一个月份"的汇总表。这在做交叉报表、数据透视时经常遇到。
行转列（PIVOT）
将纵向排列的数据转换为横向展示。
原始数据
| name | quarter | revenue |
|---|---|---|
| 张三 | Q1 | 10000 |
| 张三 | Q2 | 12000 |
| 张三 | Q3 | 11000 |
| 张三 | Q4 | 15000 |
| 李四 | Q1 | 8000 |
| 李四 | Q2 | 9000 |
| 李四 | Q3 | 9500 |
| 李四 | Q4 | 11000 |
期望结果
| name | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| 张三 | 10000 | 12000 | 11000 | 15000 |
| 李四 | 8000 | 9000 | 9500 | 11000 |
方法 1：CASE WHEN（通用，所有数据库都支持）
SELECT
name,
SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2,
SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3,
SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4
FROM sales
GROUP BY name;
理解 CASE WHEN 行转列的原理
对于张三的 4 行数据，CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END 只在 Q1 那行返回 10000，其他行返回 0。SUM 后就得到 10000。每个 CASE WHEN 都"挑选"出对应季度的值。
方法 2：IF 函数（MySQL 简写）
SELECT
name,
SUM(IF(quarter = 'Q1', revenue, 0)) AS Q1,
SUM(IF(quarter = 'Q2', revenue, 0)) AS Q2,
SUM(IF(quarter = 'Q3', revenue, 0)) AS Q3,
SUM(IF(quarter = 'Q4', revenue, 0)) AS Q4
FROM sales
GROUP BY name;
方法 3：crosstab（PostgreSQL）
-- PostgreSQL 使用 tablefunc 扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
'SELECT name, quarter, revenue FROM sales ORDER BY 1, 2',
'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(name TEXT, Q1 NUMERIC, Q2 NUMERIC, Q3 NUMERIC, Q4 NUMERIC);
列转行（UNPIVOT）
将横向排列的数据转换为纵向展示——行转列的逆操作。
原始数据
| name | math | english | science |
|---|---|---|---|
| 张三 | 90 | 85 | 92 |
| 李四 | 78 | 90 | 85 |
期望结果
| name | subject | score |
|---|---|---|
| 张三 | math | 90 |
| 张三 | english | 85 |
| 张三 | science | 92 |
| 李四 | math | 78 |
| 李四 | english | 90 |
| 李四 | science | 85 |
方法 1：UNION ALL（通用）
SELECT name, 'math' AS subject, math AS score FROM scores
UNION ALL
SELECT name, 'english', english FROM scores
UNION ALL
SELECT name, 'science', science FROM scores
ORDER BY name, subject;
方法 2：CROSS JOIN + CASE（更灵活）
-- 构造科目列表，然后交叉连接
SELECT
s.name,
t.subject,
CASE t.subject
WHEN 'math' THEN s.math
WHEN 'english' THEN s.english
WHEN 'science' THEN s.science
END AS score
FROM scores s
CROSS JOIN (
SELECT 'math' AS subject
UNION ALL SELECT 'english'
UNION ALL SELECT 'science'
) t;
方法 3：LATERAL（PostgreSQL）
-- PostgreSQL 使用 LATERAL + VALUES
SELECT s.name, t.subject, t.score
FROM scores s,
LATERAL (VALUES
('math', s.math),
('english', s.english),
('science', s.science)
) AS t(subject, score);
实战场景
场景：月度销售报表
将每日销售数据转换为月度列展示：
-- 原始数据：日期级别的销售记录
-- 目标：每个商品一行，每个月一列
SELECT
product_name,
SUM(CASE WHEN MONTH(sale_date) = 1 THEN amount ELSE 0 END) AS "1月",
SUM(CASE WHEN MONTH(sale_date) = 2 THEN amount ELSE 0 END) AS "2月",
SUM(CASE WHEN MONTH(sale_date) = 3 THEN amount ELSE 0 END) AS "3月",
-- ...
SUM(CASE WHEN MONTH(sale_date) = 12 THEN amount ELSE 0 END) AS "12月",
SUM(amount) AS "全年合计"
FROM sales
WHERE YEAR(sale_date) = 2024
GROUP BY product_name;
场景：学生成绩透视表
-- 每个学生各科成绩横向展示
SELECT
s.name,
MAX(CASE WHEN c.subject = '数学' THEN sc.score END) AS 数学,
MAX(CASE WHEN c.subject = '英语' THEN sc.score END) AS 英语,
MAX(CASE WHEN c.subject = '物理' THEN sc.score END) AS 物理
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
GROUP BY s.name;
为什么用 MAX 而不是 SUM？
因为每个学生每门课只有一个成绩，MAX 和 SUM 效果一样。但如果用 SUM，当某门课有多次考试时可能会加错。语义上 MAX 更准确——"取这门课的成绩"。
场景：多维数据透视
-- 按渠道 × 月份的订单数透视表
SELECT
channel,
SUM(CASE WHEN month = '2024-01' THEN orders ELSE 0 END) AS "2024-01",
SUM(CASE WHEN month = '2024-02' THEN orders ELSE 0 END) AS "2024-02",
SUM(CASE WHEN month = '2024-03' THEN orders ELSE 0 END) AS "2024-03",
SUM(orders) AS total
FROM (
SELECT
channel,
DATE_FORMAT(order_date, '%Y-%m') AS month,
COUNT(*) AS orders
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'
GROUP BY channel, DATE_FORMAT(order_date, '%Y-%m')
) monthly
GROUP BY channel
-- 添加合计行
UNION ALL
SELECT
'合计' AS channel,
SUM(CASE WHEN month = '2024-01' THEN orders ELSE 0 END),
SUM(CASE WHEN month = '2024-02' THEN orders ELSE 0 END),
SUM(CASE WHEN month = '2024-03' THEN orders ELSE 0 END),
SUM(orders)
FROM (
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS orders
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
) monthly;
场景：JSON 格式的动态列转行
-- MySQL 5.7+：将 JSON 数组展开为多行
SELECT
id,
JSON_UNQUOTE(JSON_EXTRACT(tags, CONCAT('$[', n.n, ']'))) AS tag
FROM articles
CROSS JOIN (
SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4
) n
WHERE JSON_EXTRACT(tags, CONCAT('$[', n.n, ']')) IS NOT NULL;
-- MySQL 8.0+：用 JSON_TABLE 更简洁
SELECT a.id, t.tag
FROM articles a,
JSON_TABLE(a.tags, '$[*]' COLUMNS (tag VARCHAR(50) PATH '$')) t;
行列转换的陷阱
陷阱 1：NULL 值处理
-- 如果某个季度没有数据，CASE WHEN 返回的是 NULL 而不是 0
-- ❌ 结果中会有 NULL
SELECT name,
SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS Q1 -- 没有 ELSE，默认 NULL
FROM sales GROUP BY name;
-- ✅ 使用 ELSE 0 或 COALESCE
SELECT name,
COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN revenue END), 0) AS Q1
FROM sales GROUP BY name;
陷阱 2：数据类型不一致
-- UNION ALL 列转行时，所有列的数据类型必须一致
-- ❌ 可能报错：math 是 INT，但 'math' 是 VARCHAR
SELECT name, CAST('math' AS CHAR) AS subject, CAST(math AS DECIMAL) AS score FROM scores
UNION ALL
SELECT name, 'english', english FROM scores;
陷阱 3：列名包含特殊字符
-- 月份、日期等作为列名时，需要用反引号（MySQL）或双引号（PostgreSQL）包裹
SELECT
product,
SUM(CASE WHEN month = '2024-01' THEN amount END) AS `2024-01`, -- MySQL
SUM(CASE WHEN month = '2024-02' THEN amount END) AS "2024-02" -- PostgreSQL
FROM sales GROUP BY product;
常见面试问题
Q1: 行转列有几种方法？分别适用什么场景？
答案：
| 方法 | 数据库支持 | 适用场景 |
|---|---|---|
| CASE WHEN + 聚合 | 所有数据库 ✅ | 列名已知且固定 |
| IF 函数 | MySQL | 简写版 CASE WHEN |
| PIVOT | SQL Server、Oracle | 原生支持 |
| crosstab | PostgreSQL | 需要 tablefunc 扩展 |
| 动态 SQL | 所有 | 列名不确定时（运行时生成列名） |
最通用的方案是 CASE WHEN + 聚合函数，面试中一定要会写。
Q2: 如果行转列的列名不确定怎么办？
答案：
需要使用动态 SQL，在运行时拼接出列名：
-- MySQL 动态行转列（存储过程）
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('SUM(CASE WHEN quarter = ''', quarter, ''' THEN revenue ELSE 0 END) AS `', quarter, '`')
) INTO @sql
FROM sales;
SET @sql = CONCAT('SELECT name, ', @sql, ' FROM sales GROUP BY name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
在实际工作中，动态列转换通常在应用层或 BI 工具中完成，而不是纯 SQL。
Q3: UNION 和 UNION ALL 有什么区别？
答案：
| 对比 | UNION | UNION ALL |
|---|---|---|
| 去重 | ✅ 自动去重 | ❌ 保留所有行 |
| 性能 | 慢（需要排序去重） | 快 |
| 使用建议 | 确实需要去重时 | 列转行、明确无重复时 |
在做列转行时，通常用 UNION ALL 而不是 UNION，因为数据本身不会重复，而且 UNION ALL 性能更好。
Q4: 面试题——将学生成绩从列展示转为行展示
原始数据：
| name | math | english | science |
|---|---|---|---|
| 张三 | 90 | 85 | 92 |
答案：
SELECT name, 'math' AS subject, math AS score FROM scores WHERE math IS NOT NULL
UNION ALL
SELECT name, 'english', english FROM scores WHERE english IS NOT NULL
UNION ALL
SELECT name, 'science', science FROM scores WHERE science IS NOT NULL;
Q5: 面试题——给定订单数据，统计每个用户每个月的消费金额（行转列）
原始数据（orders 表）：
| user_id | order_date | amount |
|---|---|---|
| 1001 | 2024-01-05 | 200 |
| 1001 | 2024-02-12 | 350 |
| 1001 | 2024-03-08 | 180 |
| 1002 | 2024-01-20 | 500 |
| 1002 | 2024-03-15 | 120 |
期望结果（每个用户一行，每个月一列）：
| user_id | 1月 | 2月 | 3月 |
|---|---|---|---|
| 1001 | 200 | 350 | 180 |
| 1002 | 500 | 0 | 120 |
答案：
-- 用 CASE WHEN + SUM 实现行转列
-- SUM 可以汇总同一用户同一月份的多笔订单
SELECT
user_id,
-- ELSE 0 保证没有消费的月份显示 0 而不是 NULL
SUM(CASE WHEN MONTH(order_date) = 1 THEN amount ELSE 0 END) AS `1月`,
SUM(CASE WHEN MONTH(order_date) = 2 THEN amount ELSE 0 END) AS `2月`,
SUM(CASE WHEN MONTH(order_date) = 3 THEN amount ELSE 0 END) AS `3月`,
SUM(amount) AS 合计
FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY user_id
ORDER BY user_id;
扩展：如果需要展示全年 12 个月
按同样的模式把 4 月到 12 月的 CASE WHEN 补全即可。实际工作中，如果月份不固定，可以用存储过程动态拼接 SQL（参考 Q2 的动态 SQL 方案）。
Q6: 面试题——某门课有多次考试成绩，行转列时应该用 SUM 还是 MAX？
场景：exam_scores 表记录了每次考试的成绩，同一个学生同一门课可能有多次考试。
| student_id | subject | score |
|---|---|---|
| 1001 | 数学 | 75 |
| 1001 | 数学 | 88 |
| 1001 | 英语 | 90 |
| 1002 | 数学 | 82 |
答案：
选择哪个聚合函数取决于业务语义：
-- ✅ 用 MAX：取该科目的最高分（"最好成绩"报表）
SELECT
student_id,
MAX(CASE WHEN subject = '数学' THEN score END) AS 数学最高分,
MAX(CASE WHEN subject = '英语' THEN score END) AS 英语最高分
FROM exam_scores
GROUP BY student_id;
-- 结果：1001 的数学 = 88（两次中取最高）
-- ✅ 用 SUM：累计该科目的总分（"总分"报表）
SELECT
student_id,
SUM(CASE WHEN subject = '数学' THEN score ELSE 0 END) AS 数学总分,
SUM(CASE WHEN subject = '英语' THEN score ELSE 0 END) AS 英语总分
FROM exam_scores
GROUP BY student_id;
-- 结果：1001 的数学 = 163（75 + 88）
-- ✅ 用 AVG：取该科目的平均分（"平均成绩"报表）
SELECT
student_id,
ROUND(AVG(CASE WHEN subject = '数学' THEN score END), 1) AS 数学均分,
ROUND(AVG(CASE WHEN subject = '英语' THEN score END), 1) AS 英语均分
FROM exam_scores
GROUP BY student_id;
| 聚合函数 | 语义 | 适用场景 |
|---|---|---|
MAX | 取最高分 | 每科只有一个成绩时也适用（结果与 SUM 等同） |
SUM | 累加所有成绩 | 计算总分、销售额累加 |
AVG | 取平均分 | 多次考试取平均成绩 |
COUNT | 统计次数 | 统计参加了几次考试 |
常见错误
如果每个学生每门课只有一个成绩，MAX 和 SUM 结果相同。但一旦有重复行，SUM 会累加出错误结果。因此在不确定数据是否唯一时，用 MAX 语义上更安全。
Q7: 列转行时，如何处理 NULL 值？
场景：student_scores 宽表中，有些学生没有参加某门课的考试，对应列为 NULL。
| name | math | english | science |
|---|---|---|---|
| 张三 | 90 | 85 | NULL |
| 李四 | NULL | 78 | 88 |
如果直接用 UNION ALL 展开，结果中会包含 score IS NULL 的行，通常不希望显示"没参加考试"的记录。
答案：
-- ❌ 不过滤 NULL：结果中包含"张三-science-NULL"和"李四-math-NULL"
SELECT name, 'math' AS subject, math AS score FROM student_scores
UNION ALL
SELECT name, 'english' AS subject, english AS score FROM student_scores
UNION ALL
SELECT name, 'science' AS subject, science AS score FROM student_scores
ORDER BY name, subject;
-- ✅ 用 WHERE IS NOT NULL 过滤：只保留有成绩的行
SELECT name, 'math' AS subject, math AS score FROM student_scores WHERE math IS NOT NULL
UNION ALL
SELECT name, 'english' AS subject, english AS score FROM student_scores WHERE english IS NOT NULL
UNION ALL
SELECT name, 'science' AS subject, science AS score FROM student_scores WHERE science IS NOT NULL
ORDER BY name, subject;
-- 结果（干净无 NULL）：
-- 张三 | english | 85
-- 张三 | math | 90
-- 李四 | english | 78
-- 李四 | science | 88
另一种写法：在外层过滤
如果已经写好了 UNION ALL，也可以用子查询在外层统一过滤：
SELECT * FROM (
SELECT name, 'math' AS subject, math AS score FROM student_scores
UNION ALL
SELECT name, 'english' AS subject, english AS score FROM student_scores
UNION ALL
SELECT name, 'science' AS subject, science AS score FROM student_scores
) t
WHERE score IS NOT NULL
ORDER BY name, subject;
两种方式效果相同，但在每个 SELECT 中过滤（第一种）性能更好，因为数据库可以在读取时就跳过 NULL 行，减少 UNION ALL 合并的数据量。
Q8: 面试题——将宽表的多个指标列转换为指标名 + 指标值两列（UNPIVOT）
场景：user_metrics 表记录了每个用户多个行为指标，需要转为指标名 + 指标值的格式，方便统一处理。
原始数据：
| user_id | page_views | clicks | purchases |
|---|---|---|---|
| 1001 | 120 | 35 | 5 |
| 1002 | 80 | 20 | 2 |
期望结果：
| user_id | metric_name | metric_value |
|---|---|---|
| 1001 | page_views | 120 |
| 1001 | clicks | 35 |
| 1001 | purchases | 5 |
| 1002 | page_views | 80 |
| 1002 | clicks | 20 |
| 1002 | purchases | 2 |
答案：
-- 通用方案：UNION ALL（所有数据库都支持）
-- 每个指标列对应一个 SELECT，用字符串字面量作为指标名
SELECT user_id, 'page_views' AS metric_name, page_views AS metric_value FROM user_metrics
UNION ALL
SELECT user_id, 'clicks' AS metric_name, clicks AS metric_value FROM user_metrics
UNION ALL
SELECT user_id, 'purchases' AS metric_name, purchases AS metric_value FROM user_metrics
ORDER BY user_id, metric_name;
-- PostgreSQL 方案：LATERAL + VALUES（更简洁，只扫描一次表）
SELECT
u.user_id,
m.metric_name,
m.metric_value
FROM user_metrics u,
LATERAL (VALUES
('page_views', u.page_views),
('clicks', u.clicks),
('purchases', u.purchases)
) AS m(metric_name, metric_value)
ORDER BY u.user_id, m.metric_name;
-- SQL Server / Oracle 原生 UNPIVOT 语法
SELECT user_id, metric_name, metric_value
FROM user_metrics
UNPIVOT (
metric_value FOR metric_name IN (page_views, clicks, purchases)
) AS unpvt
ORDER BY user_id, metric_name;
| 方案 | 数据库支持 | 优点 |
|---|---|---|
UNION ALL | 所有数据库 ✅ | 最通用，易理解 |
LATERAL + VALUES | PostgreSQL ✅ | 只扫描一次表，性能更好 |
UNPIVOT 语法 | SQL Server、Oracle ✅ | 语法简洁，原生支持 |
CROSS JOIN + CASE | 所有数据库 ✅ | 灵活，适合指标列数多时 |
注意数据类型一致性
UNION ALL 要求所有 SELECT 的对应列类型兼容。如果 page_views 是 INT 而 conversion_rate 是 FLOAT，合并为 metric_value 时需要用 CAST 统一类型，否则会报错或精度丢失。
相关链接
- MySQL GROUP_CONCAT
- MySQL JSON_TABLE
- PostgreSQL crosstab
- PostgreSQL LATERAL
- SQL PIVOT 教程
- 复杂聚合 - ROLLUP、CUBE 实现小计和总计
- 聚合函数与 GROUP BY - 行转列中常用的聚合函数