SQL与数据库开发（四）：CASE WHEN 与“行转列/列转行”花式玩法

在企业级应用的开发中后端程序员和报表工程师往往面临着一种天然的矛盾“数据库的存储格式”与“前端的展示格式”是完全不匹配的。关系型数据库最喜欢“瘦长”的表不断往下插入新行而业务方和老板最喜欢看的是“宽表”交叉表格、数据透视表。比如数据库里存的是每个员工每个月的流水记录但前端页面要求展示成“姓名 | 1月 | 2月 | 3月”的网格形式。如果把这种转换逻辑全部扔到 Java/Go 的内存里去做你需要写大量的对象映射、嵌套Map和遍历逻辑。实际上利用 SQL 中的CASE WHEN结合聚合函数我们可以直接在数据库执行层优雅地完成“行列转换”。一、 经典痛点行转列Pivot需求说明业务方需要一张学生成绩透视表要求一行展示一个学生的所有科目成绩。表结构参考瘦长型student_scores(student_name, subject, score) 里面存储的数据是流水形式的张三语文85张三数学90李四语文80使用 CASE WHEN 的标准解法这是业务开发中最常用的 SQL 魔法业界称之为“条件聚合Conditional Aggregation”。SELECT student_name, SUM(CASE WHEN subject 语文 THEN score ELSE 0 END) AS chinese_score, SUM(CASE WHEN subject 数学 THEN score ELSE 0 END) AS math_score, SUM(CASE WHEN subject 英语 THEN score ELSE 0 END) AS english_score, SUM(score) AS total_score -- 顺手把总分也算出来 FROM student_scores GROUP BY student_name;原理解析与避坑指南为什么要加SUM或MAX很多新手写行转列时会漏掉聚合函数。如果不加SUM直接使用CASE WHEN数据库依然会返回三行数据只是不匹配的科目会显示为 0。加上聚合函数并配合GROUP BY student_name就能将张三的三行数据“压扁”成一行。SUM还是MAX如果一个学生一门课只有一条成绩记录用SUM和MAX结果是一样的。但如果业务场景是“取该科目的历史最高分”则必须用MAX。可移植性MySQL 开发者喜欢用IF(subject 语文, score, 0)。虽然写起来更短但IF是 MySQL 独有的语法。如果你希望你的 SQL 能在 PostgreSQL 或 Oracle 上无缝运行请永远坚持使用标准的CASE WHEN。二、 历史包袱列转行Unpivot需求说明由于早年表结构设计不合理财务表把四个季度的营收硬编码成了四个物理字段。现在前端需要用 Echarts 画时间序列折线图需要把宽表拆回流水表。表结构参考扁宽型financial_reports(company_id, q1_revenue, q2_revenue, q3_revenue, q4_revenue)使用 UNION ALL 的标准解法对于“列转行”最直观、最兼容的方法就是使用UNION ALL进行结果集的垂直拼接。SELECT company_id, Q1 AS quarter, q1_revenue AS revenue FROM financial_reports WHERE q1_revenue IS NOT NULL UNION ALL SELECT company_id, Q2 AS quarter, q2_revenue AS revenue FROM financial_reports WHERE q2_revenue IS NOT NULL UNION ALL SELECT company_id, Q3 AS quarter, q3_revenue AS revenue FROM financial_reports WHERE q3_revenue IS NOT NULL UNION ALL SELECT company_id, Q4 AS quarter, q4_revenue AS revenue FROM financial_reports WHERE q4_revenue IS NOT NULL;工程价值把扁平的列拆成标准的行后你就可以继续对这个结果集使用GROUP BY进行更复杂的统计或者直接丢给前端的图表组件渲染。注意使用UNION ALL而不是UNION前者不进行去重计算性能高出数倍。三、 轻量级合并行转字符串GROUP_CONCAT有时候前端并不要求把科目单独拆成列只是希望在一个格子里面显示所有的标签或流水记录。需求说明查询每个用户的所有偏好标签用逗号隔开。表结构参考user_tags(user_id, tag_name)优雅解法SELECT user_id, GROUP_CONCAT(tag_name ORDER BY tag_name SEPARATOR , ) AS all_tags FROM user_tags GROUP BY user_id;原理解析GROUP_CONCAT是极其强大的文本聚合函数它甚至允许你在合并的过程中先进行ORDER BY排序或者自定义分隔符。它可以瞬间消灭应用层代码里的String.join()操作。四、 架构边界什么时候不应该用 SQL 行列转换以上介绍的都是静态行列转换列数是固定的比如四季、三门课。 如果在实际业务中你的列是动态的例如电商属性今天多了一个“屏幕刷新率”明天多了一个“键盘手感”你想写一个 SQL 自动把这些未知的属性全部转成列。结论是纯 SQL 无法直接实现动态行转列。虽然可以通过拼接字符串执行PREPARE动态语句来硬搞但这不仅极度难以调试还会引发 SQL 注入风险。面对动态透视需求正确的架构做法是将流水数据查询出来交给专业的 BI 工具如 Tableau、Metabase或者前端的 DataGrid 组件进行内存渲染。让数据库只干 I/O 和过滤的苦力活。