MySQL交叉表（透视表）转换的4种实现方式

问题背景与数据准备

假设我们有一张业务流水表tx，其中c1代表商品类别（如A1~A4），c2代表销售区域或月份（如B1~B4），c3为销售额。目标是将c2的各个唯一值横向展开为列标题，同时按c1进行行分组统计，并计算各分项与总计的和。

第1步：创建测试表并插入样本数据

首先建立测试结构，并填充具有重复键值的宽表数据：

CREATE TABLE tx(
  id INT PRIMARY KEY,
  c1 CHAR(2),
  c2 CHAR(2),
  c3 INT
);

INSERT INTO tx VALUES 
(1 ,'A1','B1',9), (2 ,'A2','B1',7), (3 ,'A3','B1',4), (4 ,'A4','B1',2),
(5 ,'A1','B2',2), (6 ,'A2','B2',9), (7 ,'A3','B2',8), (8 ,'A4','B2',5),
(9 ,'A1','B3',1), (10 ,'A2','B3',8), (11 ,'A3','B3',8), (12 ,'A4','B3',6),
(13,'A1','B4',8), (14,'A2','B4',2), (15,'A3','B4',6), (16,'A4','B4',9),
(17,'A1','B4',3), (18,'A2','B4',5), (19,'A3','B4',2), (20,'A4','B4',5);

查询原始数据可观察到每个c1对应多个c2记录：

+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 | A1   | B1   |    9 |
... (中间省略) ...
| 20 | A4   | B4   |    5 |
+----+------+------+------+

期望输出样式

转换后的目标格式应如下所示，左侧为分类C1，上方为细分项B1-B4，右下角为各类别总和及全局总计：

+------+-----+-----+-----+-----+-------+
| C1   | B1  | B2  | B3  | B4  | Total |
+------+-----+-----+-----+-----+-------+
| A1   |  9  |  2  |  1  | 11  |    23 |
| A2   |  7  |  9  |  8  |  7  |    31 |
| A3   |  4  |  8  |  8  |  8  |    28 |
| A4   |  2  |  5  |  6  | 14  |    27 |
| Total| 22  | 24  | 23  | 40  |   109 |
+------+-----+-----+-----+-----+-------+

实现方案详解

第2步：方案一 - SUM(IF) 配合 WITH ROLLUP 与子查询

此方法通过嵌套子查询实现。内层使用GROUP BY ... WITH ROLLUP对(c1, c2)组合进行多级分组，外层再通过IFNULL将空白层级替换为'Total'标签，并利用二次聚合完成列向统计。

SELECT
  IFNULL(c1,'total') AS total,
  SUM(IF(c2='B1',c3,0)) AS B1,
  SUM(IF(c2='B2',c3,0)) AS B2,
  SUM(IF(c2='B3',c3,0)) AS B3,
  SUM(IF(c2='B4',c3,0)) AS B4,
  SUM(IF(c2='total',c3,0)) AS total
FROM (
  SELECT c1, IFNULL(c2,'total') AS c2, SUM(c3) AS c3
  FROM tx
  GROUP BY c1,c2 WITH ROLLUP
  HAVING c1 IS NOT NULL
) AS A
GROUP BY c1 WITH ROLLUP;

注意： 这种写法利用了MySQL 5.7及以下版本的特性，在较新版本中可能需要调整HAVING条件以避免性能警告或语法错误。

第3步：方案二 - UNION 手动拼接汇总行

若不喜欢多层嵌套，可以将明细汇总与总计汇总拆分为两个独立的查询，并使用UNION合并。该方法逻辑清晰，易于维护，且不需要依赖特定版本的ROLLUP行为。

-- 第一部分：各分类详细统计
SELECT c1,
       SUM(IF(c2='B1',C3,0)) AS B1,
       SUM(IF(c2='B2',C3,0)) AS B2,
       SUM(IF(c2='B3',C3,0)) AS B3,
       SUM(IF(c2='B4',C3,0)) AS B4,
       SUM(C3) AS TOTAL
FROM tx
GROUP BY C1

UNION

-- 第二部分：全局总计行
SELECT 'TOTAL',
       SUM(IF(c2='B1',C3,0)) AS B1,
       SUM(IF(c2='B2',C3,0)) AS B2,
       SUM(IF(c2='B3',C3,0)) AS B3,
       SUM(IF(c2='B4',C3,0)) AS B4,
       SUM(C3)
FROM TX;

第4步：方案三 - 简化版 SUM(IF) + WITH ROLLUP

这是最简洁的单表查询写法。直接在顶层查询中使用GROUP BY C1 WITH ROLLUP，配合IFNULL处理最后一条记录的标识即可。无需子查询，性能优于前两种复杂写法。

SELECT 
  IFNULL(c1,'total'),
  SUM(IF(c2='B1',C3,0)) AS B1,
  SUM(IF(c2='B2',C3,0)) AS B2,
  SUM(IF(c2='B3',C3,0)) AS B3,
  SUM(IF(c2='B4',C3,0)) AS B4,
  SUM(C3) AS TOTAL
FROM tx
GROUP BY C1 WITH ROLLUP;

提示： WITH ROLLUP生成的最后一行c1字段值为NULL，因此必须用IFNULL将其转为字符串'Total'以匹配列类型。

第5步：方案四 - 动态 SQL 处理未知列

当c2的值是不确定的（例如按月统计，每个月份都动态增加一个列），上述硬编码方法不再适用。此时需要使用预处理语句（Prepared Statements）构建动态SQL。

1. 构建列表达式串： 遍历所有唯一的c2值，拼接成SUM(IF(C2='xx',C3,0)) AS xx格式。

-- 初始化变量
SET @EE = '';

-- 获取 distinct c2 并拼接字符串，去除末尾逗号
SELECT @EE := CONCAT(@EE, 
  'SUM(IF(C2=\'', C2, '\',', C3, 0)) AS ', C2, ',') 
FROM (SELECT DISTINCT C2 FROM TX) AS A;

2. 组装完整查询： 截取拼接好的列头，附加主体查询逻辑。

-- 拼接最终SQL，LEFT函数用于去掉末尾多余的逗号
SET @QQ = CONCAT(
  'SELECT ifnull(c1,\'total\'),', 
  LEFT(@EE, LENGTH(@EE)-1), 
  ', SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP'
);

3. 执行预处理： 声明、准备并执行语句。

PREPARE stmt2 FROM @QQ;
EXECUTE stmt2; -- 查看结果
DEALLOCATE PREPARE stmt2; -- 清理内存

技术要点总结

• 核心逻辑： 交叉表的本质是分组聚合。IF(条件, 值, 0)用于将非目标行的值置零，再配合SUM实现“行转列”。
• 替代函数： IF()可以替换为标准SQL中的CASE WHEN c2='B1' THEN c3 ELSE 0 END，兼容性更好。
• 汇总技巧： MySQL的GROUP BY ... WITH ROLLUP是生成统计报表合计行的利器，但需注意其返回的NULL值处理。
• 动态扩展： 面对列数未知的场景，必须结合存储过程或应用层代码生成SQL字符串，并在数据库中通过PREPARE/EXECUTE执行。

以上就是MySQL交叉表实现的详细内容，更多关于MySQL行转列优化的资料请关注本站其它相关文章！