做数据分析的同学十有八九都遇到过这种需求一张流水表里同一个人的多个属性明明是多行业务方却要你展示成一行多列。MySQL里管这个叫行转列也就是pivot透视反过来一张宽表里语文数学英语各占一列分析时又要把它们压回成一条条细行这叫列转行。MySQL官方到目前主流的8.0、8.4版本都还没有内置PIVOT关键字不像Oracle和SQL Server那样直接一行PIVOT就能搞定所以我们只能靠组合SQL语法自己实现。这篇文章我把实际项目中用过的七种行转列写法全部整理出来再把列转行的三种常见思路也一并讲透每段都会给出可直接执行的SQL、执行结果、适用场景和容易踩的坑。内容不挑版本5.7和8.0都能跑少数地方我会单独标注版本要求。适合正在写报表、做数据清洗、维护宽表或者面试前突击行列转换的朋友。1. 行转列到底在解决什么问题1.1 一个典型业务场景先看一个最经典的例子。学生成绩表一个学生一科一条记录数据长这样CREATE TABLE student_score ( id INT PRIMARY KEY AUTO_INCREMENT, student_name VARCHAR(50) COMMENT 学生姓名, subject VARCHAR(20) COMMENT 科目, score INT COMMENT 成绩 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO student_score (student_name, subject, score) VALUES (张三, 语文, 90), (张三, 数学, 85), (张三, 英语, 92), (李四, 语文, 88), (李四, 数学, 76), (李四, 英语, 95), (王五, 语文, 70), (王五, 数学, 91), (王五, 英语, 84);业务方说我不想看三行我要每行一个学生语文、数学、英语各占一列一眼就能横向对比。转换后的目标表是这样student_name语文数学英语张三908592李四887695王五709184这个操作就是行转列。它本质上做的两件事第一按学生分组让每个学生只占一行第二把科目这一列的值“升维”成多个新列再把对应的成绩填进格子。1.2 没有PIVOT的MySQL怎么想如果MySQL有PIVOT大概会写成SELECT * FROM student_score PIVOT (MAX(score) FOR subject IN (语文,数学,英语))但MySQL没有所以核心思路只能靠一个公式先GROUP BY需要保留的那个列比如student_name再对要转成列的那个枚举字段比如subject用条件判断去“分列”最后用聚合函数把每个条件分支的结果聚成一个值。条件判断一般是CASE WHEN或者IF聚合函数一般用SUM、MAX或者AVG。为什么要配聚合函数因为GROUP BY之后每个分组只剩一行但源数据里一个学生可能有多条匹配记录明细值必须通过某种聚合函数收敛成一个标量。这里有个很关键却又经常被忽略的点聚合函数会忽略NULL而CASE WHEN在没有匹配条件时不写ELSE就返回NULL所以SUM(CASE WHEN subject语文 THEN score END)能安全地把不属于语文的成绩全部忽略掉。在这个基础上不同写法无非是“条件判断怎么写”“聚合怎么收敛”“整条SQL怎么组织”三种维度的排列组合。下面七种方法就是在这些维度上各自独立的实现。2. 七种行转列方法逐一拆解2.1 方法一CASE WHEN 聚合函数这是最标准、最推荐新手掌握的写法没有之一。SELECT student_name, SUM(CASE WHEN subject 语文 THEN score ELSE 0 END) AS 语文, SUM(CASE WHEN subject 数学 THEN score ELSE 0 END) AS 数学, SUM(CASE WHEN subject 英语 THEN score ELSE 0 END) AS 英语 FROM student_score GROUP BY student_name;执行结果和上面的目标表完全一致。原理拆解一下CASE WHEN subject 语文 THEN score ELSE 0 END这一整段相当于对每一行源数据做一个“路标”是语文成绩就把分数放出来不是就放0。SUM再把这个分组内所有放出来的值相加。由于每个学生每个科目只有一条成绩相加结果就是成绩本身如果担心同一学生同一科目有多条记录你可以把SUM换成MAX取最高分、MIN取最低分或者AVG取平均分这个选择权在业务手上。适用场景科目枚举值少、固定不变比如语文数学英语、1月到12月、周一至周日。这一段里如果你写的是ELSE 0那么没成绩的科目会显示0如果不写ELSE会显示NULL。两种都有实际业务场景后面第4节专门展开讲这里先记住结论成绩为0和缺考是两种语义别混。2.2 方法二IF函数 聚合函数如果觉得CASE WHEN太长MySQL提供了一个函数版的替代品IF本质上和CASE WHEN一一对应只是更短。SELECT student_name, SUM(IF(subject 语文, score, 0)) AS 语文, SUM(IF(subject 数学, score, 0)) AS 数学, SUM(IF(subject 英语, score, 0)) AS 英语 FROM student_score GROUP BY student_name;IF的第一个参数是条件第二个是条件为真时的值第三个是条件为假时的值。这里同样可以搭配SUM、MAX、AVG使用。SUM(IF(subject语文, score, 0))和SUM(CASE WHEN subject语文 THEN score ELSE 0 END)执行计划几乎一致性能差别可以忽略。我个人的习惯是列少的时候用IF因为一行能写完列多、条件复杂比如要同时判断两个字段的时候用CASE WHEN因为CASE WHEN支持WHEN ... THEN ... WHEN ... THEN ...多分支比嵌套IF清晰得多。适用场景和方法一完全重叠两者选一个看着顺眼的就行。面试时如果前面已经写了方法一可以让面试官看完这个简写版本技术上不亏。2.3 方法三布尔运算 算术乘法这个方法比较取巧但特别能体现对MySQL隐式转换的理解。MySQL里布尔表达式返回1或0把这个结果直接和成绩相乘就能做到跟IF一模一样的效果SELECT student_name, SUM((subject 语文) * score) AS 语文, SUM((subject 数学) * score) AS 数学, SUM((subject 英语) * score) AS 英语 FROM student_score GROUP BY student_name;(subject 语文)对学生张三的数据行来说语文那一行返回1数学、英语返回0乘上score之后只有语文成绩被保留下来其他科目的成绩乘以0直接归零。SUM求和的结果自然就是语文成绩。因为0在这里是有意义的对应方法一的ELSE 0所以缺失成绩也会被显示成0这一点要么接受要么用NULLIF在外面包一层NULLIF(SUM((subject 语文) * score), 0) AS 语文这个方法看起来简短但可读性略差。新人接手代码时大概率会愣一下不知道这里的乘法在干什么。所以我建议小型项目、个人脚本、同事都熟悉这套写法时可以用正式报表的代码里还是优先方法一因为可维护性比代码短更重要。适用场景能接受缺失值显示0、团队代码风格偏函数式、或者想炫技的场景。2.4 方法四关联子查询前面三种都是“先分组再聚合”限制在于同一个学生同一个科目不能有多条明细否则SUM会把成绩累加。如果你面对的是“不存在缺失、一对一的唯一关系”的小数据集可以换个完全不同的思路不分组而是对每个目标列单独执行一次子查询取出来。SELECT DISTINCT s1.student_name, (SELECT score FROM student_score s2 WHERE s2.student_name s1.student_name AND s2.subject 语文) AS 语文, (SELECT score FROM student_score s3 WHERE s3.student_name s1.student_name AND s3.subject 数学) AS 数学, (SELECT score FROM student_score s4 WHERE s4.student_name s1.student_name AND s4.subject 英语) AS 英语 FROM student_score s1;子查询和外层表通过s1.student_name关联每次取一条对应科目的score。因为外层用了DISTINCTa同学的三科成绩在主查询里分别被三个子查询拉取最终每科一列。逻辑上特别直观查语文就写个查语文的子查询查数学就再写一个。这个方法最大的问题是性能。每处理一个学生三个子查询都要各自扫描一遍表数据量一大基本不能用。我在两三万行的小表上跑着没问题百万级直接放弃。另外如果同一个学生同一个科目存在两条记录子查询会报Subquery returns more than 1 row错误需要业务上保证唯一性或者在子查询里加MAX或者LIMIT 1兜底。适用场景小表、临时分析、列少、一对一关系明确。生产环境的大表强烈不建议。2.5 方法五自连接 GROUP BY自连接的思路是给每个科目单独做一次关联再把关联结果分组聚合SELECT s1.student_name, MAX(s2.score) AS 语文, MAX(s3.score) AS 数学, MAX(s4.score) AS 英语 FROM (SELECT DISTINCT student_name FROM student_score) s1 LEFT JOIN student_score s2 ON s2.student_name s1.student_name AND s2.subject 语文 LEFT JOIN student_score s3 ON s3.student_name s1.student_name AND s3.subject 数学 LEFT JOIN student_score s4 ON s4.student_name s1.student_name AND s4.subject 英语 GROUP BY s1.student_name;先把学生维度单独抽出来然后每科拼一次表科目在JOIN条件里过滤掉再用MAX聚合。和前面几种方法的区别在于这里没有用条件判断“分流”而是直接用JOIN的ON条件把每科记录分别挂到对应列上。MAX在这里是安全的因为每个科目只有一个分数如果同一科目有多条记录它取的是最高分这跟SUM的“累加”语义就完全不同了。这个方法的好处是JOIN条件里能写更复杂的关联逻辑比如带时间范围、带状态过滤扩展性比CASE WHEN强。坏处是科目每多一列就要多写一个LEFT JOIN三科还不觉得三十科的时候SQL直接变成一坨。另外如果某个学生某科没成绩LEFT JOIN会补NULLMAX会忽略NULL所以缺失值天然显示NULL语义比方法三更严谨。适用场景科目数量少、关联条件复杂、需要保留NULL语义的查询。如果科目列很多往下看方法七的动态SQL。2.6 方法六笛卡尔积 LEFT JOIN补位这个思路更“矩阵化”。先把“学生集合”和“科目集合”做笛卡尔积生成一个完整的二维网格再用LEFT JOIN把原始成绩填充到每个格子上最后按学生聚合提取各科值SELECT r.student_name, MAX(IF(c.subject 语文, s.score, NULL)) AS 语文, MAX(IF(c.subject 数学, s.score, NULL)) AS 数学, MAX(IF(c.subject 英语, s.score, NULL)) AS 英语 FROM (SELECT DISTINCT student_name FROM student_score) r CROSS JOIN (SELECT DISTINCT subject FROM student_score) c LEFT JOIN student_score s ON s.student_name r.student_name AND s.subject c.subject GROUP BY r.student_name;中间过程想一想学生集合3个科目集合3个笛卡尔积先产生9行每行是“某个学生某个科目”。LEFT JOIN把源表成绩匹配上去后比如“张三语文”这个格子能匹配到90分“张三数学”匹配到85分全部填满。最后按学生分组用IF(c.subject 语文, s.score, NULL)只需要从“科目语文”的那一行里取出分数MAX聚合忽略NULL就得到目标结果。这个方法真正的优势不是性能而是它能保证学生集合的完整性。哪怕某个学生一门成绩都没有只要他在学生维度表里存在笛卡尔积依然会为他生成行不至于像简单GROUP BY那样直接消失。对“汇报时不能漏了某个人”的场景非常有用。代价是中间行数 学生数 × 科目数如果学生一万、科目一百就是一百万行中间结果内存和扫描成本都不容忽视。适合维度小、原始数据稀疏的场景维度一大就老实用方法一。2.7 方法七动态SQL GROUP_CONCAT前面六种写死的列名在科目固定时没问题但业务说下个月科目变成“物理、化学、生物”了你总不能每个月都改一遍SQL。方法七解决的就是“列不固定、自动生成”的需求。思路分两步先用GROUP_CONCAT把要去重的科目名拼成一个“聚合片段”再把完整SQL拼出来交给PREPARE执行。-- 步骤1调大group_concat长度防止科目多时截断 SET SESSION group_concat_max_len 102400; -- 步骤2生成每个科目的聚合片段 SET sql NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT(SUM(IF(subject , subject, , score, 0)) AS , subject, ) ) INTO sql FROM student_score; -- 步骤3组装成完整SQL SET sql CONCAT(SELECT student_name, , sql, FROM student_score GROUP BY student_name ORDER BY student_name); -- 步骤4查看拼出来的SQL确认无误 SELECT sql; -- 步骤5执行 PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;执行前可以先SELECT sql;看一眼拼好的完整语句正常情况下是SELECT student_name, SUM(IF(subject 语文, score, 0)) AS 语文, SUM(IF(subject 数学, score, 0)) AS 数学, SUM(IF(subject 英语, score, 0)) AS 英语 FROM student_score GROUP BY student_name ORDER BY student_name步骤2里GROUP_CONCAT会扫描一次记录所有科目步骤5再执行一次查询所以扫描次数比方法一多一次。但如果科目列表是从别的地方维护的比如配置表你可以只在科目表变化时才重新拼SQL本质上把“生成SQL”和“执行SQL”分离了灵活性是前面几种没法比的。这个方法有两个特别坑的细节下面单列一节细说先说结论第一科目名如果包含单引号或者反引号拼接时会把SQL炸了一定要预处理第二GROUP_CONCAT默认最长1024字节科目一多直接截断拼出来的SQL语法残缺执行就是莫名其妙的报错。适用场景科目维度经常变化、报表平台自动渲染列、数据仓库层需要自动建模。这是生产环境中用得最多的高级版本。2.8 七种方法之间的差异对比平时回答“行转列有几种方式”的时候很多人会把七种说成“CASE WHEN、IF、动态SQL”三种实际上条件和聚合的组合方式远不止这些。下面这张表是我自己项目里常用的选型参考方法核心特征性能缺失值处理列固定难点CASE WHEN SUM条件分支最清晰单表扫描最快可0可NULL静态无IF SUM函数简写单表扫描最快可0可NULL静态无布尔运算乘法隐式转换技巧单表扫描最快通常显示0静态可读性差关联子查询每列独立拉取多子查询最慢天然NULL静态只适合小表自连接JOIN条件分流多次表连接天然NULL静态列多会膨胀笛卡尔积补位先铺网格再填充中间行膨胀天然NULL静态维度要小动态SQL GROUP_CONCAT自动生成列多扫一次可0可NULL动态拼接与注入安全如果只让我记两条数据量和列都固定无脑选方法一列不固定直接选方法七。中间那几种各有各的场景但都属于“特定情况下的优化方案”不是日常首选。3. 列转行的三种主流实现列转行就是行转列的逆向操作。宽表转长表在数据分析里同样常见业务系统导出来的是每个学生一行、语文数学英语各一列但BI工具做趋势分析、做图例展示时需要subject和score两列纵向排列。先准备一张宽表就用上面行转列的结果CREATE TABLE score_wide AS SELECT student_name, 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 student_score GROUP BY student_name;目标表长这样student_namesubjectscore张三语文90张三数学85张三英语92李四语文88.........3.1 方法一UNION ALL纵向合并最简单直接一行SQL搞定SELECT student_name, 语文 AS subject, 语文 AS score FROM score_wide UNION ALL SELECT student_name, 数学 AS subject, 数学 AS score FROM score_wide UNION ALL SELECT student_name, 英语 AS subject, 英语 AS score FROM score_wide ORDER BY student_name, subject;每一段SELECT负责“拆”一列把列名固定成值把列值放进统一的score列里三段结果再纵向拼回一张表。UNION ALL和UNION的区别这里必须强调UNION会对结果做去重如果张三四李四恰好同时有一科成绩相同UNION会把重复行吃掉数据就丢了。所以列转行里永远用UNION ALL除非你确实要去重。这个方法通用性最强不管是MySQL 5.7还是8.0都能跑列数可控时我每次都优先用它。缺点是如果宽表有几十列你得写几十段UNION ALL代码特别啰嗦维护起来想骂人。3.2 方法二CROSS JOIN CASE WHEN不想写那么多UNION ALL那就把“要拆哪些列”作为一张维度表用CROSS JOIN把宽表和维度表做配对再用CASE WHEN按维度值取对应的列SELECT w.student_name, sub.subject, CASE sub.subject WHEN 语文 THEN w.语文 WHEN 数学 THEN w.数学 WHEN 英语 THEN w.英语 END AS score FROM score_wide w CROSS JOIN ( SELECT 语文 AS subject UNION ALL SELECT 数学 UNION ALL SELECT 英语 ) sub ORDER BY w.student_name, sub.subject;CROSS JOIN让每个学生和三个科目值各配一次所以每人固定拆出三行CASE sub.subject每次只会从宽表对应列中取一个值填进score。这个写法适合科目维度同时在其他地方也有配置表的情况只需要维护一张科目维度表宽表就算有更多列也不用反复改UNION ALL里的重复模板。需要注意的一点如果宽表里某科成绩是NULLCASE WHEN返回NULL转出来的长表里score也是NULL这是正常的。如果你想转出来的结果不包含NULL行在外面套一层WHERE score IS NOT NULL即可。3.3 方法三字符串拆分 数字辅助表有些场景更极端数据压根就不在宽表列里而是在一列里面用逗号拼成了一长串。比如某张表student_namesubject_score张三语文:90,数学:85,英语:92李四语文:88,数学:76,英语:95这通常是GROUP_CONCAT或者前端拼接留下来的中间产物。要把它还原成明细行就必须做字符串拆分。MySQL里没有现成的SPLIT函数最土但最兼容的做法是借助数字辅助表CREATE TABLE nums (n INT PRIMARY KEY); INSERT INTO nums VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); SELECT t.student_name, SUBSTRING_INDEX(SUBSTRING_INDEX(t.subject_score, ,, nums.n), ,, -1) AS item FROM score_concat t JOIN nums ON CHAR_LENGTH(t.subject_score) - CHAR_LENGTH(REPLACE(t.subject_score, ,, )) nums.n - 1;这里CHAR_LENGTH(t.subject_score) - CHAR_LENGTH(REPLACE(t.subject_score, ,, ))算出字符串里逗号的数量逗号数量 1就是拆分行数JOIN数字表保证每串最多能拆出n行。SUBSTRING_INDEX两层套用是“先截到第n段再取最后一段”的经典拆分技巧。MySQL 8.0还可以用递归CTE拆得更顺手WITH RECURSIVE split AS ( SELECT student_name, SUBSTRING_INDEX(subject_score, ,, 1) AS item, SUBSTRING(subject_score, LOCATE(,, subject_score) 1) AS rest FROM score_concat WHERE subject_score IS NOT NULL UNION ALL SELECT student_name, SUBSTRING_INDEX(rest, ,, 1), SUBSTRING(rest, LOCATE(,, rest) 1) FROM split WHERE rest ) SELECT student_name, item FROM split;这个方法的适用范围比较窄绝大多数情况下你根本不应该让一串逗号拼接的脏数据出现在表里它只适合“数据已经这样了、你只能凑合用”的窘境。真正建模时优先UNION ALL其次CROSS JOIN别把字符串拆分当成常规手段。3.4 列转行的注意事项列转行看起来是行转列的逆过程但坑不太一样。第一UNION ALL各段SELECT的列顺序和类型要保持一致MySQL虽然会自动类型转换但从字符串列拆出来的数字如果被隐式转成字符串后续做聚合排序就可能出幺蛾子。第二ORDER BY写在最后一段SELECT后面才对整个结果生效写在前面的SELECT里会被忽略。第三如果宽表列名有中文或者特殊字符不要忘了反引号。另外列转行之后如果有多个指标列比如成绩和排名UNION ALL只能拆一列拆多列需要把所有指标组合列同时SELECT代码量会翻倍。这时候可以考虑用JSON函数MySQL 8.0里可以用JSON_OBJECTAGG把一行变成JSON再展开但是写法更绕日常处理里我一般不用知道有这么个思路就行。4. 高频踩坑与排查技巧行列转换的报错十有八九不是语法问题而是数据语义问题。这里把我实际踩过、也在同事代码里见过的坑集中整理一下每条都配了排查思路。4.1 NULL与0的语义陷阱行转列的CASE WHEN里ELSE 0和ELSE NULL是两种完全不同的业务语义。ELSE 0表示“这个学生这个科目成绩是0”通常用于没有歧义的计分场景比如出勤次数缺勤就是0ELSE NULL也就是不写ELSE或者写THEN score END不兜底表示“这个学生这个科目没有记录”适合成绩表因为缺考不等于0分。如果用了SUM配合不写ELSE的CASE WHEN要明白SUM会忽略NULL但只要分组里有一条匹配SUM就会把非NULL值正常加总。当一个学生完全没有语文记录时SUM(CASE WHEN subject语文 THEN score END) 的结果是NULL而不是0。如果报表里显示成了NULL而前端不兼容就用COALESCE兜底转成0注意这是展示层的处理不要污染源数据语义。用MAX配合NULL时函数自动忽略NULL所以缺科天然显示NULL这比SUM更符合“缺失即空”的直觉。排查这类问题最简单的办法把聚合函数去掉只跑一遍CASE WHEN的明细结果看NULL分布是否和预想一致。4.2 GROUP_CONCAT长度截断方法七里的动态SQL如果科目数量多或者生成的聚合片段长GROUP_CONCAT默认的1024字节限制会导致拼接结果被截断。最典型的报错是PREPARE之后提示语法错误你盯着拼出来的SQL看半天发现语句不完整但其实根因在更早的拼接阶段。解决办法是先执行一句SET SESSION group_concat_max_len 102400;session级设置只对当前连接生效放到连接池里记得每次连接都要重新设置或者直接写在存储过程开头。如果业务上科目数量可能上千干脆设置成更大的值或者SET SESSION group_concat_max_len 1024 * 1024 * 10;一次到位。4.3 动态SQL注入风险动态SQL把字段名拼进SQL安全隐患比想象中大。如果科目名来自用户输入比如报表平台里用户自己填了一个“物理 OR 11”直接拼到SQL里就是注入。就算不是恶意攻击科目名里带单引号或反引号也会直接把SQL语法炸掉。处理思路两条一是在拼接前过滤特殊字符用REPLACE把单引号替换掉比如REPLACE(subject, , )二是加白名单校验只允许中英文、数字和下划线。实测下来第二种更安全因为替换只能防注入防不了列名变得不可读。另外动态SQL拼出来的列名一定要用反引号包裹否则列名和SQL关键字撞车就麻烦了。4.4 性能对比与选型建议最后整理一个选型速查逻辑直接照着选列固定、数据量大方法一或方法二单表扫描性能最好。实测百万行数据这两个方案毫秒级完成。列不固定、需要自动适配方法七动态SQL。注意group_concat_max_len和注入过滤。数据稀疏、学生维度必须完整方法六笛卡尔积补位。小表快速分析、列少方法四子查询写法最直观。同科多条记录、需要取最大值把SUM换成MAX哪种方法都行优先方法一。宽表转长表无脑UNION ALL列超级多时用CROSS JOIN 维度表。我自己做项目时的原则很简单能写死的列绝对不动态拼因为静态SQL可读性和可维护性最好列真会变的场景必须用方法七那就把拼接逻辑封装成存储过程把特殊字符清洗放在拼接之前避免每次都踩坑。最后再啰嗦两句行转列这类题背下来七种写法不如理解一句话先分组、再分列、最后聚合。分列的方式可以是CASE WHEN、IF、布尔乘法、子查询、JOIN连接、笛卡尔积也可以是动态拼接聚合的方式无非SUM、MAX、MIN、AVG但把两者组合起来就能应对绝大多数业务场景。列转行则是反向操作核心是“一列拆成值、多列拼成行”UNION ALL永远是地基。你如果自己实操一遍一定会发现不同方法跑出来的结果差异主要集中在NULL处理和同一学生同一科目多行明细这两个点上。建议先造一个有“缺科”和“重复分”的数据集把七种方法都跑一遍对比一下它们的输出差异。这样下次无论是写报表还是面试你都能说得清为什么选这个方法而不选那个这比单纯背SQL片段有价值得多。
