如何在MySQL中通过DML语句实现数据行的行列转换？-mysql教程-PHP中文网

mysql不支持pivot，需用case when+max()+group by实现行转列：先按分组键聚合，再用case筛选值、max压平多行并忽略null，确保每组每列仅一个有效值。

如何在mysql中通过dml语句实现数据行的行列转换？

MySQL里没有PIVOT函数，别硬套SQL Server写法

MySQL原生不支持 PIVOT 和 UNPIVOT 语法，直接写 SELECT * FROM t PIVOT(...) 会报错 ERROR 1064 (42000): You have an error in your SQL syntax。这不是版本问题，是设计上就没这功能——得用条件聚合+分组绕过去。

常见错误是照搬其他数据库的写法，或者试图用子查询嵌套拼接列，结果要么语法报错，要么返回笛卡尔积或NULL值过多。

核心思路：用 CASE WHEN + MAX()（或 SUM()/GROUP_CONCAT()）把行值“投射”到不同列
必须搭配 GROUP BY，否则聚合函数会把整表压成一行
如果源数据中同一分组有多个非空值，MAX() 取的是字典序最大值，不是最新时间戳——这点容易误判结果

用CASE WHEN + GROUP BY做静态行列转换

适用于列名固定、已知要转成哪几列的场景，比如把用户各科成绩从“科目-分数”行结构转成“语文|数学|英语”三列。

假设表 score 结构为 (user_id, subject, score)，想按 user_id 汇总出每科一列：

SELECT
  user_id,
  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 score
GROUP BY user_id;

注意点：

CASE WHEN 的每个分支必须对应明确的字符串字面量，不能用变量或子查询动态生成列名
若某用户缺某科成绩，对应列就是 NULL，不是 0；需要默认值就加 COALESCE(..., 0)
如果 score 是字符串类型，MAX() 仍有效，但语义可能偏离预期（比如取“张三”而不是“李四”）

动态列？只能靠应用层拼接SQL

当要转的列名来自数据本身（比如不同用户有不同标签），MySQL无法在单条DML里动态生成列名。所谓“动态行列转换”，本质是先查出所有可能的列名，再拼出带 CASE WHEN 的SQL字符串，在应用代码里执行。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

例如：先运行 SELECT DISTINCT tag FROM user_tags ORDER BY tag; 得到 ['vip', 'new', 'active']，再构造：

SELECT user_id,
  MAX(CASE WHEN tag = 'vip' THEN 1 ELSE 0 END) AS vip,
  MAX(CASE WHEN tag = 'new' THEN 1 ELSE 0 END) AS new,
  MAX(CASE WHEN tag = 'active' THEN 1 ELSE 0 END) AS active
FROM user_tags
GROUP BY user_id;

关键限制：

MySQL存储过程里也不能用变量替换列名（PREPARE + EXECUTE 只能换值，不能换结构）
试图用 GROUP_CONCAT 把多行拼成一列再解析，属于反模式——破坏了关系型语义，且无法被索引加速
如果列数超过几百个，生成的SQL可能触发 max_allowed_packet 限制

UNPIVOT等价写法：用UNION ALL展开固定列

把宽表变长表（比如把 (id, name, dept_a, dept_b, dept_c) 转成 (id, name, dept_name, dept_value)），就得反向操作：对每一列写一个 SELECT，用 UNION ALL 合并。

示例：

SELECT id, name, 'dept_a' AS dept_name, dept_a AS dept_value FROM t WHERE dept_a IS NOT NULL
UNION ALL
SELECT id, name, 'dept_b', dept_b FROM t WHERE dept_b IS NOT NULL
UNION ALL
SELECT id, name, 'dept_c', dept_c FROM t WHERE dept_c IS NOT NULL;

注意：