企业官网建设流程全解析

做数据分析的同学，十有八九都遇到过这种需求：一张流水表里同一个人的多个属性明明是多行，业务方却要你展示成一行多列。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 '成绩' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO student_score (student_name, subject, score) VALUES ('张三', '语文', 90), ('张三', '数学', 85), ('张三', '英语', 92), ('李四', '语文', 88), ('李四', '数学', 76), ('李四', '英语', 95), ('王五', '语文', 70), ('王五', '数学', 91), ('王五', '英语', 84);

业务方说，我不想看三行，我要每行一个学生，语文、数学、英语各占一列，一眼就能横向对比。转换后的目标表是这样：

这个操作就是行转列。它本质上做的两件事：第一，按学生分组，让每个学生只占一行；第二，把科目这一列的值“升维”成多个新列，再把对应的成绩填进格子。

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。因为外层用了DISTINCT，a同学的三科成绩在主查询里分别被三个子查询拉取，最终每科一列。逻辑上特别直观：查语文就写个查语文的子查询，查数学就再写一个。

这个方法最大的问题是性能。每处理一个学生，三个子查询都要各自扫描一遍表，数据量一大基本不能用。我在两三万行的小表上跑着没问题，百万级直接放弃。另外，如果同一个学生同一个科目存在两条记录，子查询会报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会补NULL，MAX会忽略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”三种，实际上条件和聚合的组合方式远不止这些。下面这张表是我自己项目里常用的选型参考：

如果只让我记两条：数据量和列都固定，无脑选方法一；列不固定，直接选方法七。中间那几种各有各的场景，但都属于“特定情况下的优化方案”，不是日常首选。

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;

目标表长这样：

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里的重复模板。

需要注意的一点：如果宽表里某科成绩是NULL，CASE WHEN返回NULL，转出来的长表里score也是NULL，这是正常的。如果你想转出来的结果不包含NULL行，在外面套一层WHERE score IS NOT NULL即可。

3.3 方法三：字符串拆分 + 数字辅助表

有些场景更极端，数据压根就不在宽表列里，而是在一列里面用逗号拼成了一长串。比如某张表：

这通常是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”，通常用于没有歧义的计分场景，比如出勤次数，缺勤就是0；ELSE 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 '1'='1”，直接拼到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片段有价值得多。