做报表的时候一定都遇到过这种鬼需求底表明明是一行一个学生的成绩记录偏偏要你把语文、数学、英语全铺开成一行。我早年第一次跑这种需求卡了一个下午才拼出第一版SQL后来做得多了才发现所谓的“行转列”说穿了就是“分组 条件聚合”而“列转行”就是反过来把固定列拆成行。这篇文章把这套东西彻底讲透MySQL 行转列的7种方法加上列转行的几种常用写法把原理、SQL、坑点一次说完。无论是平时写报表、做数据分析还是准备面试都能直接抄作业。1. 行转列与列转行先搞懂场景和本质1.1 什么时候需要行转列MySQL里大部分业务表为了扩展方便存的都是流水式长表一行就是一个事实记录。比如学生成绩表就是name, subject, score三列每个学生三条记录。这种表的优势很明显——加一个新维度不需要改表结构直接插入新行就行。但报表端通常不这么看它需要的是宽表一行一个学生语文、数学、英语各占一列横向对比谁偏科一眼就能看出来。典型的场景包括成绩分析每个学生多科成绩要展示成一行多列销售汇总每个销售员连续几个月的业绩要横向排开渠道对比不同渠道的转化率、客单价等指标放在一行里对比列转行则是反向操作。有些宽表因为历史原因或者导入工具的原因已经做成了一列一个指标的样子比如每个月一列可后续要做聚合计算、要接入BI系统又需要把所有列拆回长表。这两个动作本质上是同一件事的两种方向改变数据的“组织方式”但信息量和数据总量不改变。1.2 行转列的核心逻辑分组 条件聚合行转列之所以能在SQL里完成靠的就是GROUP BY加条件聚合。你想转成几个列就在SELECT里写几个条件聚合表达式每个表达式负责“从这一组所有行中取出符合某个条件的值”。这个逻辑有两个关键点想明白之后所有方法都能看懂。第一GROUP BY的粒度必须和转成一行后的维度一致。比如每个学生一行就按name分组每个销售员一行就按sales_id分组。第二聚合函数怎么选择取决于每个分组内部同一个科目是否只有一条记录。只有一条记录时用MAX和SUM结果一样有多条记录时MAX取最大值SUM做累加COUNT做计数业务语义完全不同。列转行反过来本质是把一行的几个固定列“枚举”成多行。常见手段是UNION ALL或者用交叉连接生成一组“列名”再配合CASE取值。理解了这两句话后面的代码就全是套路了。2. 行转列7种方法从固定列到动态列在动手之前先准备一张测试表后面所有方法都在同一份数据上验证CREATE TABLE t_score ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), subject VARCHAR(20), score INT ); INSERT INTO t_score (name, subject, score) VALUES (张三, 语文, 90), (张三, 数学, 85), (张三, 英语, 92), (李四, 语文, 88), (李四, 数学, 76), (李四, 英语, 81);目标结果name 语文 数学 英语 张三 90 85 92 李四 88 76 812.1 方法一CASE WHEN 聚合函数最经典的写法先把最常用的模板亮出来SELECT 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 t_score GROUP BY name;这段话值得好好拆一下。CASE WHEN部分负责筛选当subject 语文时返回score否则不写ELSEMySQL会默认返回NULL。外层的MAX对组内所有行取最大值由于每组内一个科目只有一条记录取到的就是分数本身组合在一起就相当于“把语文这一列的值从行里揪出来放到新列里”。很多人问为什么用MAX而不是SUM主要原因就是MAX能天然忽略NULL。如果某个学生缺考语文这一组语文对应的CASE返回NULLMAX仍然能取出数学和英语的成绩不会被NULL干扰。用SUM也能忽略NULL但语义会变成累加如果同一个学生同一科目存在多条记录结果就直接错了所以这个写法在“一对一”场景下更稳妥。2.2 方法二IF() 替代 CASE WHEN少写几行代码老MySQL代码里更常见的是IF()函数语法比CASE WHEN短一截SELECT name, MAX(IF(subject 语文, score, NULL)) AS 语文, MAX(IF(subject 数学, score, NULL)) AS 数学, MAX(IF(subject 英语, score, NULL)) AS 英语 FROM t_score GROUP BY name;IF()有三个参数条件、真值、假值。这里假值写的是NULL不是0。这个区别很重要——假值写NULL意味着“这个科目没考”假值写0意味着“考了但考了0分”在统计平均分时一个是被忽略一个是拉低平均分业务结论完全不同。方法一和方法二本质上没有任何区别只是语法糖不同。我个人的建议是看团队习惯如果只写MySQLIF()确实短如果代码可能要被其他数据库兼容CASE WHEN是标准SQL迁移成本低。2.3 方法三SUM(IF()) 做累加和计数别和MAX混用你还可以把聚合函数换掉用SUM(IF())SELECT name, SUM(IF(subject 语文, score, 0)) AS 语文, SUM(IF(subject 数学, score, 0)) AS 数学, SUM(IF(subject 英语, score, 0)) AS 英语 FROM t_score GROUP BY name;这种写法在“一个学生一个科目只有一条成绩”的数据下结果和方法一完全一样。但如果同一个学生同一个科目有多条记录比如补考、模考、平时分和期末分都存在SUM会把所有分数全部加起来这时候出来的“总分”很可能不是你想要的东西。SUM条件聚合还有一个特别惊艳的用法是计数。在MySQL里布尔表达式直接返回0或1所以可以这么写SELECT name, SUM(subject 语文) AS 语文次数, SUM(subject 数学) AS 数学次数, SUM(subject 英语) AS 英语次数 FROM t_score GROUP BY name;这相当于用SUM把“每个学生参加了几次语文考试”数出来比COUNT(CASE WHEN ...)短得多面试题里很爱考。2.4 方法四GROUP_CONCAT 拼接快速预览不折腾有时候你只是想快速看看每个学生的科目和成绩不想写一长串的CASE WHENGROUP_CONCAT就是最省事的SELECT name, GROUP_CONCAT( CONCAT(subject, :, score) ORDER BY FIELD(subject, 语文, 数学, 英语) SEPARATOR | ) AS subject_score FROM t_score GROUP BY name;结果会变成这样name subject_score 张三 语文:90 | 数学:85 | 英语:92 李四 语文:88 | 数学:76 | 英语:81这样确实没有拆成独立列但它解决的是另一类需求只需要展示、不参与计算。GROUP_CONCAT支持ORDER BY子句来控制拼接顺序也支持SEPARATOR自定义分隔符默认是英文逗号。需要注意它有一个隐藏坑默认最大长度只有1024字节超过部分会静默截断数据多了必须先执行SET SESSION group_concat_max_len 1048576;再查询。2.5 方法五动态SQL PREPARE列不固定时的最终方案前面几种写法有一个共同的限制列名是写死的。如果科目是动态增加的比如这个季度有“历史”下个季度又多了“地理”每次都去改SQL显然不现实。这时候必须上动态SQL。思路分三步第一步查出所有科目拼出“条件聚合片段”第二步把片段拼成完整SQL第三步用PREPARE和EXECUTE执行。SET sql NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( MAX(IF(subject , subject, , score, NULL)) AS , subject, ) ) INTO sql FROM t_score; SET sql CONCAT(SELECT name, , sql, FROM t_score GROUP BY name); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;这个方法看着简单实际操作里有几个非常容易踩的坑。第一个是单引号转义在MySQL字符串里表示一个单引号要写成两个单引号所以在CONCAT里会看到连续两个包住字段值少写一个就会拼出语法错误的SQL。第二个是列名必须加反引号否则科目名如果是关键字或者带空格执行时直接报错。第三个还是GROUP_CONCAT长度问题科目数量多的话同样要调group_concat_max_len。建议在正式跑之前先单独执行一下中间那段SELECT看拼出来的列定义片段长什么样确认无误再拼完整SQL。动态SQL的灵活性换来的是牺牲可读性维护时需要格外小心。2.6 方法六临时表 LEFT JOIN最直观但最啰嗦如果科目很少而且是固定的还可以用“每个科目查一个子查询再JOIN起来”的方式SELECT a.name, a.score AS 语文, b.score AS 数学, c.score AS 英语 FROM (SELECT name, score FROM t_score WHERE subject 语文) a LEFT JOIN (SELECT name, score FROM t_score WHERE subject 数学) b ON a.name b.name LEFT JOIN (SELECT name, score FROM t_score WHERE subject 英语) c ON a.name c.name;逻辑非常直白先把语文成绩查出来作为主表再依次把数学、英语成绩用LEFT JOIN挂上去。但缺点同样明显每个科目都要单独扫一次表科目一多性能就会变差SQL也会越来越长。如果有学生缺考某科LEFT JOIN会产生NULL需要额外用COALESCE处理。这个方案适合教学演示或者数据量小、列数固定的临时需求。2.7 方法七GROUP_CONCAT SUBSTRING_INDEX思路扩展但慎用最后一种方法是把数据先拼接成一个带分隔符的串再按位置拆分成列SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(gs, |, 1), |, -1) AS col1, SUBSTRING_INDEX(SUBSTRING_INDEX(gs, |, 2), |, -1) AS col2, SUBSTRING_INDEX(SUBSTRING_INDEX(gs, |, 3), |, -1) AS col3 FROM ( SELECT name, GROUP_CONCAT(score ORDER BY subject SEPARATOR |) AS gs FROM t_score GROUP BY name ) t;这段SQL的核心是SUBSTRING_INDEX(SUBSTRING_INDEX(gs, |, n), |, -1)它的作用是从拼接串里取出第n段。能跑通但不推荐在生产环境用必须预先知道一共有几列而且GROUP_CONCAT的排序规则决定了顺序一旦排序变了拆出来的列就对应错了。这里把它列出来是为了把思路补全——当你看到一段“行转列”代码里出现SUBSTRING_INDEX连环嵌套时知道它的原理是什么也明白它为什么不能通用。到这里7种方法就齐了我整理了一张对比表方便选型方法适用场景列是否动态性能推荐度CASE WHEN MAX列固定、一对一否优推荐IF() MAX列固定、一对一否优推荐SUM(IF())多条明细累加/计数否优视语义GROUP_CONCAT快速预览否中展示用动态SQL PREPARE列不固定是中推荐子查询 LEFT JOIN列少、教学演示否中不推荐SUBSTRING_INDEX思路参考否低不建议3. 列转行把宽表拆回长表3.1 UNION ALL 拆列最直接的方式宽表转长表最直接的方式就是UNION ALL。假设现在有一张宽表CREATE TABLE t_score_wide ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), 语文 INT, 数学 INT, 英语 INT ); INSERT INTO t_score_wide (name, 语文, 数学, 英语) VALUES (张三, 90, 85, 92), (李四, 88, 76, 81);要把“语文、数学、英语”三个列拆成三行只需要对每一列写一条SELECT然后用UNION ALL连起来SELECT name, 语文 AS subject, 语文 AS score FROM t_score_wide UNION ALL SELECT name, 数学 AS subject, 数学 AS score FROM t_score_wide UNION ALL SELECT name, 英语 AS subject, 英语 AS score FROM t_score_wide;注意必须用UNION ALL而不是UNION。UNION会做去重如果张三语文和李四语文恰好考了相同的分数UNION会把这两行合并成一行数据直接变少属于隐蔽性很强的错误。UNION ALL不会去重只是简单堆叠在这里才是正确选择。这种写法最大的缺点是代码重复——每多一个列就要把整段SELECT再复制一遍。如果表里有几十个月份的业绩列这个SQL会长到没法看。3.2 CROSS JOIN CASE避免整段复制更好的做法是先把列名枚举出来再用CASE WHEN从宽表里取对应值SELECT w.name, t.subject, CASE t.subject WHEN 语文 THEN w.语文 WHEN 数学 THEN w.数学 WHEN 英语 THEN w.英语 END AS score FROM t_score_wide w CROSS JOIN ( SELECT 语文 AS subject UNION ALL SELECT 数学 UNION ALL SELECT 英语 ) t;这里CROSS JOIN的作用是让宽表里的每一行和三个科目名做笛卡尔积一行变三行。CASE再根据枚举出来的科目名从对应列取值。这个方案的好处是每增加一个科目只需要在枚举里加一行、在CASE里加一个WHENSELECT部分不会越来越臃肿。代码量在列多的情况下比UNION ALL少很多也更清晰。3.3 MySQL 8.0 的JSON_TABLE更灵活的扩展路子MySQL 8.0支持JSON_TABLE函数可以把JSON数据转成表。有同学问过列很多、名字又有规律比如1月、2月、3月能不能动态拆可以借助JSON_TABLE把一行构造成一个JSON数组再展开SELECT w.name, jt.subject, jt.score FROM t_score_wide w JOIN JSON_TABLE( JSON_ARRAY( JSON_OBJECT(subject, 语文, score, w.语文), JSON_OBJECT(subject, 数学, score, w.数学), JSON_OBJECT(subject, 英语, score, w.英语) ), $[*] COLUMNS ( subject VARCHAR(20) PATH $.subject, score INT PATH $.score ) ) jt;这个方案思路很巧先把一行的几个列值组装成一个JSON数组$[*]表示遍历数组中每个元素COLUMNS声明产出字段。但它有一个很现实的问题——每个列仍然要手写一遍JSON_OBJECT代码量并不会比UNION ALL少多少而且JSON构造有额外开销。真正的价值在“列名由程序生成、JSON结构可动态构造”的场景比如从配置表里读出所有列再生成JSON这时它能做到完全动态。大部分普通报表用不到知道有这条路就行。4. 常见问题与实测避坑4.1 动态SQL的引号地狱动态SQL写多了最折磨人的就是引号嵌套。在MySQL里字符串内部表示一个单引号需要连续写两个单引号当这个字符串本身又是CONCAT的参数时两层引号叠在一起经常一个字没注意就拼坏了。我现在的习惯是拼之前先在注释里把目标SQL完整手写一遍比如“我要拼出来的SQL是SELECT name, MAX(IF(subject 语文, score, NULL)) AS 语文 FROM t_score GROUP BY name”。然后把其中的文本部分按层拆开再填进CONCAT。实在不确定时先单独查一下拼出来的sql内容看到完整SQL再执行能省很多排查时间。动态SQL一旦出错报错信息指向的是“执行期”而不是“拼装期”如果不打印中间变量排错全靠猜。4.2 GROUP_CONCAT 长度截断GROUP_CONCAT的默认最大长度是1024字节超过之后MySQL不会报错而是静默截断。这就很坑了——你可能以为自己拼出了完整结果实际上后半段已经丢了并且完全没有提示。遇到拼接列很多、内容很长的场景执行查询前先设置一下SET SESSION group_concat_max_len 1048576;我建议用SESSION而不是GLOBAL因为这是会话级的配置只在当前连接生效不会影响其他业务。长度设成1024 * 1024通常够用了实在不够再往上加。4.3 ONLY_FULL_GROUP_BY 的兼容问题MySQL 5.7之后默认开启了ONLY_FULL_GROUP_BY它要求SELECT里的非聚合列必须出现在GROUP BY中。行转列这个场景一般不会触发问题因为GROUP BY name之后SELECT里除了name全是聚合表达式。但列转行遇到老版本迁移过来的SQL就容易翻车。比如有人习惯在GROUP BY name后直接SELECT name, score这在新版本会直接报错。解决办法有两个一是改SQL把score也加进GROUP BY或者改成聚合二是关掉模式。我不建议动全局配置MySQL 8.0里这个模式默认开启就是希望开发者写更严格的SQL换个版本环境你未必还记得关过这个开关。4.4 NULL 和 0别傻傻分不清条件聚合里假值返回NULL和假值返回0的语义差别非常大。用MAX(CASE WHEN subject语文 THEN score END)取语文成绩如果这个学生没有语文成绩得到的是NULL表示“缺考”用SUM(IF(subject语文, score, 0))没有语文成绩时得到0看起来像“考了0分”。这两个值在展示、计算时都不同。算平均分时NULL被直接忽略0分会计入总分把平均分拉低。处理数据之前先问清楚业务的规则缺考到底算NULL还是0同一组内有多条明细时到底取最大值还是累加业务语义没对齐后面的SQL写得再快也白搭。4.5 7种方法到底怎么选最后说说选型。看到7种方法新手容易陷入纠结实际上判断标准很简单列固定、数量少用CASE WHEN MAX稳妥、可读性强列不固定用动态SQL PREPARE改配置就能适应新增科目只需要临时看数据用GROUP_CONCAT快速预览别上报表教学或验证逻辑用子查询 LEFT JOIN最直观大数据量生产环境优先CASE WHEN GROUP BY条件聚合能走索引避免多次子查询和字符串拆分另外很多人纠结用SUM还是MAX其实在大数据量下更关键的是GROUP BY能否走索引。给(name, subject)建立联合索引行转列的分组效率会有明显提升。条件聚合本身只是行内计算真正的瓶颈在分组数据量。最后说点实际操作里的体会。行转列和列转行这两组SQL语法本身并不难难的是写之前把业务语义想清楚缺考算NULL还是0同一分组有多条明细要不要先去重科目列表是稳定的还是经常变这些问题不解决SQL写得再花哨也可能得出错误结论。我自己的习惯是先把“目标表长什么样”画出来再决定用静态列还是动态列、用MAX还是SUM。至于这7种方法建议你都在本地跑一遍——尤其是那种“能通但我不推荐”的方案踩过一次坑才能真正理解为什么别人推荐另一种写法。 
长河君 · 2026/10/16 18:04:53 · 阅读 1.2k MySQL行转列7种方法+列转行实现：从原理到SQL实战避坑指南
做报表的时候一定都遇到过这种鬼需求底表明明是一行一个学生的成绩记录偏偏要你把语文、数学、英语全铺开成一行。我早年第一次跑这种需求卡了一个下午才拼出第一版SQL后来做得多了才发现所谓的“行转列”说穿了就是“分组 条件聚合”而“列转行”就是反过来把固定列拆成行。这篇文章把这套东西彻底讲透MySQL 行转列的7种方法加上列转行的几种常用写法把原理、SQL、坑点一次说完。无论是平时写报表、做数据分析还是准备面试都能直接抄作业。1. 行转列与列转行先搞懂场景和本质1.1 什么时候需要行转列MySQL里大部分业务表为了扩展方便存的都是流水式长表一行就是一个事实记录。比如学生成绩表就是name, subject, score三列每个学生三条记录。这种表的优势很明显——加一个新维度不需要改表结构直接插入新行就行。但报表端通常不这么看它需要的是宽表一行一个学生语文、数学、英语各占一列横向对比谁偏科一眼就能看出来。典型的场景包括成绩分析每个学生多科成绩要展示成一行多列销售汇总每个销售员连续几个月的业绩要横向排开渠道对比不同渠道的转化率、客单价等指标放在一行里对比列转行则是反向操作。有些宽表因为历史原因或者导入工具的原因已经做成了一列一个指标的样子比如每个月一列可后续要做聚合计算、要接入BI系统又需要把所有列拆回长表。这两个动作本质上是同一件事的两种方向改变数据的“组织方式”但信息量和数据总量不改变。1.2 行转列的核心逻辑分组 条件聚合行转列之所以能在SQL里完成靠的就是GROUP BY加条件聚合。你想转成几个列就在SELECT里写几个条件聚合表达式每个表达式负责“从这一组所有行中取出符合某个条件的值”。这个逻辑有两个关键点想明白之后所有方法都能看懂。第一GROUP BY的粒度必须和转成一行后的维度一致。比如每个学生一行就按name分组每个销售员一行就按sales_id分组。第二聚合函数怎么选择取决于每个分组内部同一个科目是否只有一条记录。只有一条记录时用MAX和SUM结果一样有多条记录时MAX取最大值SUM做累加COUNT做计数业务语义完全不同。列转行反过来本质是把一行的几个固定列“枚举”成多行。常见手段是UNION ALL或者用交叉连接生成一组“列名”再配合CASE取值。理解了这两句话后面的代码就全是套路了。2. 行转列7种方法从固定列到动态列在动手之前先准备一张测试表后面所有方法都在同一份数据上验证CREATE TABLE t_score ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), subject VARCHAR(20), score INT ); INSERT INTO t_score (name, subject, score) VALUES (张三, 语文, 90), (张三, 数学, 85), (张三, 英语, 92), (李四, 语文, 88), (李四, 数学, 76), (李四, 英语, 81);目标结果name 语文 数学 英语 张三 90 85 92 李四 88 76 812.1 方法一CASE WHEN 聚合函数最经典的写法先把最常用的模板亮出来SELECT 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 t_score GROUP BY name;这段话值得好好拆一下。CASE WHEN部分负责筛选当subject 语文时返回score否则不写ELSEMySQL会默认返回NULL。外层的MAX对组内所有行取最大值由于每组内一个科目只有一条记录取到的就是分数本身组合在一起就相当于“把语文这一列的值从行里揪出来放到新列里”。很多人问为什么用MAX而不是SUM主要原因就是MAX能天然忽略NULL。如果某个学生缺考语文这一组语文对应的CASE返回NULLMAX仍然能取出数学和英语的成绩不会被NULL干扰。用SUM也能忽略NULL但语义会变成累加如果同一个学生同一科目存在多条记录结果就直接错了所以这个写法在“一对一”场景下更稳妥。2.2 方法二IF() 替代 CASE WHEN少写几行代码老MySQL代码里更常见的是IF()函数语法比CASE WHEN短一截SELECT name, MAX(IF(subject 语文, score, NULL)) AS 语文, MAX(IF(subject 数学, score, NULL)) AS 数学, MAX(IF(subject 英语, score, NULL)) AS 英语 FROM t_score GROUP BY name;IF()有三个参数条件、真值、假值。这里假值写的是NULL不是0。这个区别很重要——假值写NULL意味着“这个科目没考”假值写0意味着“考了但考了0分”在统计平均分时一个是被忽略一个是拉低平均分业务结论完全不同。方法一和方法二本质上没有任何区别只是语法糖不同。我个人的建议是看团队习惯如果只写MySQLIF()确实短如果代码可能要被其他数据库兼容CASE WHEN是标准SQL迁移成本低。2.3 方法三SUM(IF()) 做累加和计数别和MAX混用你还可以把聚合函数换掉用SUM(IF())SELECT name, SUM(IF(subject 语文, score, 0)) AS 语文, SUM(IF(subject 数学, score, 0)) AS 数学, SUM(IF(subject 英语, score, 0)) AS 英语 FROM t_score GROUP BY name;这种写法在“一个学生一个科目只有一条成绩”的数据下结果和方法一完全一样。但如果同一个学生同一个科目有多条记录比如补考、模考、平时分和期末分都存在SUM会把所有分数全部加起来这时候出来的“总分”很可能不是你想要的东西。SUM条件聚合还有一个特别惊艳的用法是计数。在MySQL里布尔表达式直接返回0或1所以可以这么写SELECT name, SUM(subject 语文) AS 语文次数, SUM(subject 数学) AS 数学次数, SUM(subject 英语) AS 英语次数 FROM t_score GROUP BY name;这相当于用SUM把“每个学生参加了几次语文考试”数出来比COUNT(CASE WHEN ...)短得多面试题里很爱考。2.4 方法四GROUP_CONCAT 拼接快速预览不折腾有时候你只是想快速看看每个学生的科目和成绩不想写一长串的CASE WHENGROUP_CONCAT就是最省事的SELECT name, GROUP_CONCAT( CONCAT(subject, :, score) ORDER BY FIELD(subject, 语文, 数学, 英语) SEPARATOR | ) AS subject_score FROM t_score GROUP BY name;结果会变成这样name subject_score 张三 语文:90 | 数学:85 | 英语:92 李四 语文:88 | 数学:76 | 英语:81这样确实没有拆成独立列但它解决的是另一类需求只需要展示、不参与计算。GROUP_CONCAT支持ORDER BY子句来控制拼接顺序也支持SEPARATOR自定义分隔符默认是英文逗号。需要注意它有一个隐藏坑默认最大长度只有1024字节超过部分会静默截断数据多了必须先执行SET SESSION group_concat_max_len 1048576;再查询。2.5 方法五动态SQL PREPARE列不固定时的最终方案前面几种写法有一个共同的限制列名是写死的。如果科目是动态增加的比如这个季度有“历史”下个季度又多了“地理”每次都去改SQL显然不现实。这时候必须上动态SQL。思路分三步第一步查出所有科目拼出“条件聚合片段”第二步把片段拼成完整SQL第三步用PREPARE和EXECUTE执行。SET sql NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( MAX(IF(subject , subject, , score, NULL)) AS , subject, ) ) INTO sql FROM t_score; SET sql CONCAT(SELECT name, , sql, FROM t_score GROUP BY name); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;这个方法看着简单实际操作里有几个非常容易踩的坑。第一个是单引号转义在MySQL字符串里表示一个单引号要写成两个单引号所以在CONCAT里会看到连续两个包住字段值少写一个就会拼出语法错误的SQL。第二个是列名必须加反引号否则科目名如果是关键字或者带空格执行时直接报错。第三个还是GROUP_CONCAT长度问题科目数量多的话同样要调group_concat_max_len。建议在正式跑之前先单独执行一下中间那段SELECT看拼出来的列定义片段长什么样确认无误再拼完整SQL。动态SQL的灵活性换来的是牺牲可读性维护时需要格外小心。2.6 方法六临时表 LEFT JOIN最直观但最啰嗦如果科目很少而且是固定的还可以用“每个科目查一个子查询再JOIN起来”的方式SELECT a.name, a.score AS 语文, b.score AS 数学, c.score AS 英语 FROM (SELECT name, score FROM t_score WHERE subject 语文) a LEFT JOIN (SELECT name, score FROM t_score WHERE subject 数学) b ON a.name b.name LEFT JOIN (SELECT name, score FROM t_score WHERE subject 英语) c ON a.name c.name;逻辑非常直白先把语文成绩查出来作为主表再依次把数学、英语成绩用LEFT JOIN挂上去。但缺点同样明显每个科目都要单独扫一次表科目一多性能就会变差SQL也会越来越长。如果有学生缺考某科LEFT JOIN会产生NULL需要额外用COALESCE处理。这个方案适合教学演示或者数据量小、列数固定的临时需求。2.7 方法七GROUP_CONCAT SUBSTRING_INDEX思路扩展但慎用最后一种方法是把数据先拼接成一个带分隔符的串再按位置拆分成列SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(gs, |, 1), |, -1) AS col1, SUBSTRING_INDEX(SUBSTRING_INDEX(gs, |, 2), |, -1) AS col2, SUBSTRING_INDEX(SUBSTRING_INDEX(gs, |, 3), |, -1) AS col3 FROM ( SELECT name, GROUP_CONCAT(score ORDER BY subject SEPARATOR |) AS gs FROM t_score GROUP BY name ) t;这段SQL的核心是SUBSTRING_INDEX(SUBSTRING_INDEX(gs, |, n), |, -1)它的作用是从拼接串里取出第n段。能跑通但不推荐在生产环境用必须预先知道一共有几列而且GROUP_CONCAT的排序规则决定了顺序一旦排序变了拆出来的列就对应错了。这里把它列出来是为了把思路补全——当你看到一段“行转列”代码里出现SUBSTRING_INDEX连环嵌套时知道它的原理是什么也明白它为什么不能通用。到这里7种方法就齐了我整理了一张对比表方便选型方法适用场景列是否动态性能推荐度CASE WHEN MAX列固定、一对一否优推荐IF() MAX列固定、一对一否优推荐SUM(IF())多条明细累加/计数否优视语义GROUP_CONCAT快速预览否中展示用动态SQL PREPARE列不固定是中推荐子查询 LEFT JOIN列少、教学演示否中不推荐SUBSTRING_INDEX思路参考否低不建议3. 列转行把宽表拆回长表3.1 UNION ALL 拆列最直接的方式宽表转长表最直接的方式就是UNION ALL。假设现在有一张宽表CREATE TABLE t_score_wide ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), 语文 INT, 数学 INT, 英语 INT ); INSERT INTO t_score_wide (name, 语文, 数学, 英语) VALUES (张三, 90, 85, 92), (李四, 88, 76, 81);要把“语文、数学、英语”三个列拆成三行只需要对每一列写一条SELECT然后用UNION ALL连起来SELECT name, 语文 AS subject, 语文 AS score FROM t_score_wide UNION ALL SELECT name, 数学 AS subject, 数学 AS score FROM t_score_wide UNION ALL SELECT name, 英语 AS subject, 英语 AS score FROM t_score_wide;注意必须用UNION ALL而不是UNION。UNION会做去重如果张三语文和李四语文恰好考了相同的分数UNION会把这两行合并成一行数据直接变少属于隐蔽性很强的错误。UNION ALL不会去重只是简单堆叠在这里才是正确选择。这种写法最大的缺点是代码重复——每多一个列就要把整段SELECT再复制一遍。如果表里有几十个月份的业绩列这个SQL会长到没法看。3.2 CROSS JOIN CASE避免整段复制更好的做法是先把列名枚举出来再用CASE WHEN从宽表里取对应值SELECT w.name, t.subject, CASE t.subject WHEN 语文 THEN w.语文 WHEN 数学 THEN w.数学 WHEN 英语 THEN w.英语 END AS score FROM t_score_wide w CROSS JOIN ( SELECT 语文 AS subject UNION ALL SELECT 数学 UNION ALL SELECT 英语 ) t;这里CROSS JOIN的作用是让宽表里的每一行和三个科目名做笛卡尔积一行变三行。CASE再根据枚举出来的科目名从对应列取值。这个方案的好处是每增加一个科目只需要在枚举里加一行、在CASE里加一个WHENSELECT部分不会越来越臃肿。代码量在列多的情况下比UNION ALL少很多也更清晰。3.3 MySQL 8.0 的JSON_TABLE更灵活的扩展路子MySQL 8.0支持JSON_TABLE函数可以把JSON数据转成表。有同学问过列很多、名字又有规律比如1月、2月、3月能不能动态拆可以借助JSON_TABLE把一行构造成一个JSON数组再展开SELECT w.name, jt.subject, jt.score FROM t_score_wide w JOIN JSON_TABLE( JSON_ARRAY( JSON_OBJECT(subject, 语文, score, w.语文), JSON_OBJECT(subject, 数学, score, w.数学), JSON_OBJECT(subject, 英语, score, w.英语) ), $[*] COLUMNS ( subject VARCHAR(20) PATH $.subject, score INT PATH $.score ) ) jt;这个方案思路很巧先把一行的几个列值组装成一个JSON数组$[*]表示遍历数组中每个元素COLUMNS声明产出字段。但它有一个很现实的问题——每个列仍然要手写一遍JSON_OBJECT代码量并不会比UNION ALL少多少而且JSON构造有额外开销。真正的价值在“列名由程序生成、JSON结构可动态构造”的场景比如从配置表里读出所有列再生成JSON这时它能做到完全动态。大部分普通报表用不到知道有这条路就行。4. 常见问题与实测避坑4.1 动态SQL的引号地狱动态SQL写多了最折磨人的就是引号嵌套。在MySQL里字符串内部表示一个单引号需要连续写两个单引号当这个字符串本身又是CONCAT的参数时两层引号叠在一起经常一个字没注意就拼坏了。我现在的习惯是拼之前先在注释里把目标SQL完整手写一遍比如“我要拼出来的SQL是SELECT name, MAX(IF(subject 语文, score, NULL)) AS 语文 FROM t_score GROUP BY name”。然后把其中的文本部分按层拆开再填进CONCAT。实在不确定时先单独查一下拼出来的sql内容看到完整SQL再执行能省很多排查时间。动态SQL一旦出错报错信息指向的是“执行期”而不是“拼装期”如果不打印中间变量排错全靠猜。4.2 GROUP_CONCAT 长度截断GROUP_CONCAT的默认最大长度是1024字节超过之后MySQL不会报错而是静默截断。这就很坑了——你可能以为自己拼出了完整结果实际上后半段已经丢了并且完全没有提示。遇到拼接列很多、内容很长的场景执行查询前先设置一下SET SESSION group_concat_max_len 1048576;我建议用SESSION而不是GLOBAL因为这是会话级的配置只在当前连接生效不会影响其他业务。长度设成1024 * 1024通常够用了实在不够再往上加。4.3 ONLY_FULL_GROUP_BY 的兼容问题MySQL 5.7之后默认开启了ONLY_FULL_GROUP_BY它要求SELECT里的非聚合列必须出现在GROUP BY中。行转列这个场景一般不会触发问题因为GROUP BY name之后SELECT里除了name全是聚合表达式。但列转行遇到老版本迁移过来的SQL就容易翻车。比如有人习惯在GROUP BY name后直接SELECT name, score这在新版本会直接报错。解决办法有两个一是改SQL把score也加进GROUP BY或者改成聚合二是关掉模式。我不建议动全局配置MySQL 8.0里这个模式默认开启就是希望开发者写更严格的SQL换个版本环境你未必还记得关过这个开关。4.4 NULL 和 0别傻傻分不清条件聚合里假值返回NULL和假值返回0的语义差别非常大。用MAX(CASE WHEN subject语文 THEN score END)取语文成绩如果这个学生没有语文成绩得到的是NULL表示“缺考”用SUM(IF(subject语文, score, 0))没有语文成绩时得到0看起来像“考了0分”。这两个值在展示、计算时都不同。算平均分时NULL被直接忽略0分会计入总分把平均分拉低。处理数据之前先问清楚业务的规则缺考到底算NULL还是0同一组内有多条明细时到底取最大值还是累加业务语义没对齐后面的SQL写得再快也白搭。4.5 7种方法到底怎么选最后说说选型。看到7种方法新手容易陷入纠结实际上判断标准很简单列固定、数量少用CASE WHEN MAX稳妥、可读性强列不固定用动态SQL PREPARE改配置就能适应新增科目只需要临时看数据用GROUP_CONCAT快速预览别上报表教学或验证逻辑用子查询 LEFT JOIN最直观大数据量生产环境优先CASE WHEN GROUP BY条件聚合能走索引避免多次子查询和字符串拆分另外很多人纠结用SUM还是MAX其实在大数据量下更关键的是GROUP BY能否走索引。给(name, subject)建立联合索引行转列的分组效率会有明显提升。条件聚合本身只是行内计算真正的瓶颈在分组数据量。最后说点实际操作里的体会。行转列和列转行这两组SQL语法本身并不难难的是写之前把业务语义想清楚缺考算NULL还是0同一分组有多条明细要不要先去重科目列表是稳定的还是经常变这些问题不解决SQL写得再花哨也可能得出错误结论。我自己的习惯是先把“目标表长什么样”画出来再决定用静态列还是动态列、用MAX还是SUM。至于这7种方法建议你都在本地跑一遍——尤其是那种“能通但我不推荐”的方案踩过一次坑才能真正理解为什么别人推荐另一种写法。