拓冰网站定制 
写SQL的时间久了你会发现一个挺有意思的现象真正能拉开人与人差距的往往不是那些看起来炫技的窗口函数或几十层嵌套子查询而是一个基础到容易被忽略的关键字——CASE WHEN。它在SQL里的角色有点像炒菜时的盐单拎出来没人会多看一眼但整道菜好不好吃盐放没放到位置差别是决定性的。它可以用来做简单的字段打标更是行转列、条件聚合、分段统计、数据去重这些高频场景里的主力工具。这篇文章会把CASE WHEN的执行逻辑、实战用法、性能陷阱、面试考点一次讲透新手可以直接照着抄老手也能借这个机会查漏补缺。我这两年帮团队review了不少SQL发现大家用CASE WHEN的水平分化很严重。一拨人只会在SELECT后面加一个最简单的判断打标签碰到稍微复杂的业务就疯狂堆子查询把自己绕晕也让数据库被迫一遍遍扫表。另一拨人把CASE WHEN和聚合、窗口函数、UPDATE结合起来用一条SQL就把行转列、分组统计、优先级排序全干完了代码又短又清晰。这篇文章致力于让你成为第二拨人下面直接进正题。1. 先把CASE WHEN的底裤翻出来语法和执行逻辑1.1 两种写法别用混了CASE WHEN在SQL里一共有两种语法形态虽然绝大多数教材把它们放在一起讲但理解两者的区别非常重要面试和工作里都容易在这里栽跟头。第一种叫简单CASE表达式长得像自动售货机CASE column_name WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE resultN END它的工作方式就是拿着column_name的值逐个和WHEN后面的值做等值比较。注意只能做等值比较。也就是这种判断。第二种叫搜索CASE表达式长得像人工服务柜台CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE resultN END这种写法不限定列名WHEN后面可以放任何结果为真假的表达式。所以、、LIKE、BETWEEN、IN、IS NULL甚至一个EXISTS子查询全都能往里塞。我见过不少人在需要做范围判断的时候还在用简单CASE硬凑写出一堆WHEN score 90这种条件实际上应该用的是搜索CASE的WHEN score 90。记住这个区分别管等值判断用什么一涉及范围、模糊、空值判断请直接切到搜索CASE表达式。1.2 执行顺序是自上而下的别把条件写反CASE WHEN是顺序求解的数据库会从第一个WHEN开始往下判断哪个条件先为真就返回对应的THEN后面的条件直接不再执行。很多人写分段统计时在这里踩坑比如CASE WHEN score 60 THEN 及格 WHEN score 90 THEN 优秀 ELSE 不及格 END这条语句跑出来所有90分以上的学生都会被打成“及格”。因为90分也满足score 60数据库从头扫到第一个为真的条件就返回了“优秀”这个分支永远等不到。写分段逻辑时必须把最严格的条件放最前面CASE WHEN score 90 THEN 优秀 WHEN score 60 THEN 及格 ELSE 不及格 END这个顺序问题不止出现在CASE WHEN里但CASE WHEN最容易让人疏忽因为它看起来太简单了。我的习惯是写任何分段或优先级判断先停下来想清楚条件集合是不是互斥的。如果互斥顺序随意如果存在包含关系就按从窄到宽排。1.3 NULL和ELSE的两处大坑第一处坑是简单CASE表达式的NULL判断。下面这个写法看起来没毛病实际上永远走不到第一个分支CASE column_name WHEN NULL THEN 空值 ELSE 非空 END原因很简单SQL里NULL NULL的结果不是真也不是假而是UNKNOWN。自动售货机模式做的等值判断拿NULL去匹配NULL根本匹配不上。想判断空值必须用搜索CASECASE WHEN column_name IS NULL THEN 空值 ELSE 非空 END第二处坑是ELSE缺失。省略ELSE时所有未匹配的行会返回NULL。很多聚合场景下这个NULL会被静默丢弃。我举个例子统计线上和线下支付金额SELECT user_id, SUM(CASE WHEN pay_type online THEN amount END) AS online_amount, SUM(CASE WHEN pay_type offline THEN amount END) AS offline_amount FROM orders GROUP BY user_id这段SQL看起来没问题但实际跑出来某个用户如果全部是线上支付他的offline_amount会是NULL而不是0。表面看只是结果集里多了个空值但报表工具、Java代码、Python处理NULL的方式各不相同轻则页面显示空白重则程序抛空指针。加上ELSE 0就能把SUM的语义变成“没匹配到的记0”这是条件聚合里最常用也最该养成习惯的写法。2. 高频实战CASE WHEN到底天天在干什么活2.1 条件聚合与行转列一次扫描取代N次子查询CASE WHEN在实战中最常见的搭档是聚合函数。核心思路是用CASE WHEN把每一行“归类”并映射成目标值再用SUM、COUNT、MAX等聚合函数把同类数据压成一个标量。举个经典的行转列例子。订单表结构大致是-- 字段user_id, order_id, amount, statuspaid/refund/pending现在要统计每个用户的已支付总金额、退款总金额、待支付总金额一列一个字段。常见的新手写法是三个子查询硬拼SELECT user_id, (SELECT SUM(amount) FROM orders WHERE user_id a.user_id AND status paid) AS paid_amount, (SELECT SUM(amount) FROM orders WHERE user_id a.user_id AND status refund) AS refund_amount FROM orders a GROUP BY user_id这种写法最大的问题是性能。orders表每扫描出一个用户就要为这三个子查询各跑一次全表扫描假设没有索引或索引选择性不佳数据量一大必卡。CASE WHEN的写法是SELECT user_id, SUM(CASE WHEN status paid THEN amount ELSE 0 END) AS paid_amount, SUM(CASE WHEN status refund THEN amount ELSE 0 END) AS refund_amount, SUM(CASE WHEN status pending THEN amount ELSE 0 END) AS pending_amount FROM orders GROUP BY user_id这条SQL全程只扫一遍orders表每一行经过三个CASE判断分别落入对应的累加器一次物理扫描同时完成三个维度的汇总。原理上就是经典的“行转列”思路把行里的分类值变成结果集里的列。同样是统计执行计划差着量级这就是为什么我说CASE WHEN看着基础用好了直接决定SQL的下限。2.2 分段统计给数据打上业务标签再分组条件聚合之外的另一个高频场景是分段。比如用户表里有年龄字段想按年龄段统计人数常规做法是SELECT CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 30 THEN 青年 WHEN age BETWEEN 31 AND 50 THEN 中年 ELSE 老年 END AS age_group, COUNT(*) FROM users GROUP BY CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 30 THEN 青年 WHEN age BETWEEN 31 AND 50 THEN 中年 ELSE 老年 END我知道你一定想问SELECT里已经写了CASE表达式还起了别名age_group为什么GROUP BY不能直接写GROUP BY age_group原因出在SQL的逻辑执行顺序上。数据库实际执行的流程大致是先FROM取数据再WHERE过滤再GROUP BY分组然后才是SELECT计算字段。SELECT别名是在分组之后才生成的GROUP BY阶段根本还没这个别名。所以要用同样的CASE表达式再写一遍。当然日常开发里为了避免一大坨重复的CASE表达式更常见的做法是先用子查询或CTE把分段标签算好再在外层分组SELECT age_group, COUNT(*) FROM ( SELECT CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 30 THEN 青年 ELSE 中年及以上 END AS age_group FROM users ) t GROUP BY age_group这两种写法各有适用场景。前者适合CASE逻辑简单、只在这一个地方用到的情况后者适合CASE逻辑复杂、而且后续可能还要基于这个标签继续做关联的情况。唯一需要提醒的是MySQL在宽松的sql_mode下确实允许SELECT别名直接用在GROUP BY里但它本质上是在玩火换到别的数据库或者换一组sql_mode配置SQL立刻报错。我建议按标准写法来跨库迁移时省心。2.3 数据清洗与分组去重和ROW_NUMBER搭档效果极佳很多时候报表数据不是干净的。比如用户表里的邮箱字段有的填QQ邮箱有的填163有的填企业邮箱。要按邮箱类型统计用户分布就可以用CASE WHEN做归一化SELECT CASE WHEN email LIKE %qq.com THEN QQ邮箱 WHEN email LIKE %163.com THEN 网易邮箱 WHEN email LIKE %gmail.com THEN 谷歌邮箱 ELSE 其他 END AS email_type, COUNT(DISTINCT user_id) AS user_cnt FROM users GROUP BY CASE WHEN email LIKE %qq.com THEN QQ邮箱 WHEN email LIKE %163.com THEN 网易邮箱 WHEN email LIKE %gmail.com THEN 谷歌邮箱 ELSE 其他 END这里的COUNT(DISTINCT user_id)就带上了去重功能和CASE WHEN配合能在一次分组里完成“按规则归类和去重计数”两件事。如果去重的逻辑更复杂比如订单明细表存在重复导入每个用户的订单可能出现多条几乎相同的记录现在要保留每个用户最新一条有效订单就轮到CASE WHEN和窗口函数搭档了SELECT user_id, order_id, amount FROM ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER ( PARTITION BY user_id, order_no ORDER BY create_time DESC ) AS rn FROM orders ) t WHERE rn 1窗口函数负责为每个分组内的记录排号外层WHERE只取编号为1的记录。这套组合拳是“分组后去重取最新”的标配写法CASE WHEN在里面的作用更多是配合聚合或者过滤条件比如只统计类型为有效的订单SELECT user_id, order_id, amount FROM ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER ( PARTITION BY user_id, order_no ORDER BY create_time DESC ) AS rn, CASE WHEN status valid THEN 1 ELSE 0 END AS valid_flag FROM orders ) t WHERE rn 1 AND valid_flag 12.4 在UPDATE里做批量条件更新一条语句处理所有分支CASE WHEN不只出现在SELECT里它在UPDATE语句里的价值被严重低估。假设商品表要按库存状态调价库存为0的商品涨价10%库存低于10的商品涨价5%其余不涨价。常规做法是三条UPDATE各自跑一遍UPDATE products SET price price * 1.10 WHERE stock 0; UPDATE products SET price price * 1.05 WHERE stock 10 AND stock 0;这三条语句分开执行的问题在于每一条都要单独开启事务、写一遍日志、加一遍锁更重要的是如果某个商品同时满足多个条件比如先执行了第一条涨价10%再执行第二条又涨价5%价格就被复利式地涨了两轮。而业务本意是只按一个规则调整。用CASE WHEN一条UPDATE搞定UPDATE products SET price CASE WHEN stock 0 THEN price * 1.10 WHEN stock 10 THEN price * 1.05 ELSE price END这里有个细节值得注意CASE WHEN是顺序求解的所以价格不会重复累加。而且整条UPDATE是单语句数据库只需要写一次日志、持有一轮锁代码在执行层也不会有“多条语句之间的中间状态”问题。热词里有人搜“sql server writelog”其实就是在关心这类写操作产生的日志量。把多条UPDATE合并成一条带CASE WHEN的UPDATE是减少日志写入、改善并发性能的实践之一。当然如果本来就是三条独立业务规则需要分别记录变更历史那就别硬并是不是该合并要结合业务来judge。3. 进阶技巧把CASE WHEN用出花同时别把性能拖垮3.1 自定义排序ORDER BY里写CASE控制展示顺序数据库默认的排序只有升序降序数字按大小字符串按字典序。但业务上经常有“不按字典序排”的需求。比如订单状态要按“待处理、处理中、已完成、已取消”这个业务顺序展示而不是按拼音或字母顺序。这时候CASE WHEN就派上用场了SELECT order_id, status FROM orders ORDER BY CASE status WHEN pending THEN 1 WHEN processing THEN 2 WHEN completed THEN 3 WHEN cancelled THEN 4 ELSE 5 END原理不复杂待处理映射成1处理中映射成2数据库按映射后的数字升序排就得到了业务想要的顺序。这个技巧在报表里特别实用尤其是“合计”这一行如果想让它在最后一行展示也可以先把合计映射成一个比较大的排序值。而且ORDER BY里的CASE不会影响WHERE的执行计划在大结果集上排序虽然会占用内存或临时文件但这是排序本身的开销跟CASE表达式关系不大。3.2 与窗口函数组合条件计数不求人窗口函数是这两年SQL面试的高频考点CASE WHEN和它配合最经典的场景是“按条件计数”。比如要统计每个用户的高价值订单数量金额大于100的算高价值直觉反应可能是COUNT(amount 100)但COUNT函数只接受字段名或*不支持布尔表达式。标准做法就是用CASE WHEN把条件变成1或0再交给SUMSELECT user_id, SUM(CASE WHEN amount 100 THEN 1 ELSE 0 END) AS high_value_cnt, COUNT(*) AS total_cnt FROM orders GROUP BY user_id如果把上面的普通聚合换成窗口函数场景就变成在保留每一行订单明细的同时给每行附加“该用户总共有几笔高价值订单”这个汇总信息。这就是CASE WHEN加窗口函数的正确用法SELECT order_id, user_id, amount, SUM(CASE WHEN amount 100 THEN 1 ELSE 0 END) OVER (PARTITION BY user_id) AS user_high_value_cnt FROM orders注意OVER (PARTITION BY user_id)这个子句让SUM变成了窗口聚合它不会压缩行数而是把计算结果复制到每一行。CASE WHEN在这里的作用是“条件开关”命中条件贡献1不命中贡献0SUM加总后就是符合条件的数量。这个套路你掌握之后很多“既要明细又要汇总”的报表都能用一条SQL写出来不用再费劲JOIN一个临时聚合表。3.3 性能陷阱小心CASE WHEN帮倒忙废掉索引CASE WHEN用顺手之后容易产生一种“什么都想用CASE”的冲动但有几类写法会导致性能灾难我一个个说。第一类在WHERE条件里用CASE WHEN包住索引列。比如SELECT * FROM orders WHERE CASE WHEN status paid THEN create_time ELSE update_time END 2024-01-01这种写法从语义上讲不同的行会拿不同的字段去比较优化器无法把它转换成对create_time或update_time的范围扫描只能对全表逐行计算CASE再判断索引直接失效。遇到类似需求化整为零反而更快SELECT * FROM orders WHERE (status paid AND create_time 2024-01-01) OR (status paid AND update_time 2024-01-01)这段改写虽然出现了OR但两个分支分别可以走对应的索引取决于数据分布优化器也可能选择分别索引扫描再合并。不过在数据分布极度不均时这种OR写法也可能不如UNION ALL这个要看执行计划说话不能拍脑袋。第二类在CASE的THEN子句里放标量子查询。SELECT CASE WHEN type A THEN (SELECT MAX(price) FROM products WHERE category_id o.category_id) ELSE 999 END AS price FROM orders o如果orders表有十万行这个THEN里的子查询在极端情况下会被执行十万次有没有物化取决于优化器通常是不乐观的。遇到这类需求优先把子查询改成提前JOINSELECT CASE WHEN o.type A THEN t.max_price ELSE 999 END AS price FROM orders o LEFT JOIN ( SELECT category_id, MAX(price) AS max_price FROM products GROUP BY category_id ) t ON t.category_id o.category_id把计算提前到一次聚合里CASE WHEN只做最后的映射两者性能天差地别。3.4 慢SQL优化里的正确姿势合并扫描、减少IO热词里有“慢sql优化”这也是CASE WHEN特别能发光发热的领域。最常见的慢SQL场景是同一张表、同一种维度只是条件不同就写成三条查询再用UNION或应用层代码拼结果。比如统计今天不同类型订单的数量SELECT COUNT(*) FROM orders WHERE status paid AND create_time 2024-01-01; SELECT COUNT(*) FROM orders WHERE status refund AND create_time 2024-01-01; SELECT COUNT(*) FROM orders WHERE status pending AND create_time 2024-01-01;这三条SQL各扫一遍符合条件的行物理上就是把同一批数据读了三次。改成CASE WHEN条件聚合SELECT COUNT(CASE WHEN status paid THEN 1 END) AS paid_cnt, COUNT(CASE WHEN status refund THEN 1 END) AS refund_cnt, COUNT(CASE WHEN status pending THEN 1 END) AS pending_cnt FROM orders WHERE create_time 2024-01-01注意这里用了COUNT(CASE WHEN ... THEN 1 END)因为COUNT只统计非NULL所以没命中的行自动被忽略不需要写ELSE 0。这样一次扫描同时数出三类数量IO直接减少到三分之一。但过犹不及。如果这三次查询各自的过滤条件过滤性差别极大比如paid的订单有千万级、refund只有几十条那么分开查询配合索引可能比合并扫描更快。写SQL没有银弹CASE WHEN合并SQL只是手段真正要做的是看执行计划里的扫描行数。我见过不少人把能拆的SQL强行合并成一个巨无霸CASE结果优化器走错执行计划比原来更慢这不是CASE WHEN的锅是过度优化的锅。4. 面试现场CASE WHEN的加分答法4.1 高频面试题学生成绩行转列CASE WHEN在面试题里出境频率最高的就是行转列。题目通常是这样的有一张成绩表student_idcoursescore1语文901数学851英语882语文752数学92要求查成student_id语文数学英语190858827592NULL标准答案SELECT student_id, MAX(CASE WHEN course 语文 THEN score END) AS chinese_score, MAX(CASE WHEN course 数学 THEN score END) AS math_score, MAX(CASE WHEN course 英语 THEN score END) AS english_score FROM score GROUP BY student_id这里很多初学者会问为什么用MAX取最大值不是把其他成绩丢掉了吗实际上在这个场景里同一学生同一科目分组后只有一个值MAX不是“取最大”而是“从一组里挑出那个非NULL的值”。GROUP BY后每组只有一行scoreCASE负责把不属于当前科目的score置为NULLMAX自然就选到了剩下的那个值。这个题的关键不是MAX函数本身而是理解GROUP BY加CASE WHEN的“填表”过程。同理也可以用MIN。4.2 加分的深入点如果同一科目有多条记录怎么办面试官往往会在你答完基础版本后追问如果同一个学生同一个科目有多条考试成绩我只想取最近一次的分数怎么写这就是进阶考点。要分两步先用窗口函数给每个学生每门课按考试时间排序再在排序结果基础上做行转列SELECT student_id, MAX(CASE WHEN course 语文 THEN score END) AS chinese_score, MAX(CASE WHEN course 数学 THEN score END) AS math_score FROM ( SELECT student_id, course, score, ROW_NUMBER() OVER ( PARTITION BY student_id, course ORDER BY exam_time DESC ) AS rn FROM score ) t WHERE rn 1 GROUP BY student_id这个写法把“每组取最新一条”的问题拆成两步窗口函数负责排序和筛选外层CASE WHEN负责旋转。能主动讲出这一步面试官对你的评价会明显不一样因为这证明你不是只会背模板而是理解了两个基础能力窗口函数的行内排名、CASE WHEN的列内映射如何组合。4.3 容易翻车的三个点能答对就是加分项面试里考察CASE WHEN很多时候不是让你写一段复杂的SQL而是让你挑错。我总结三个高频翻车点。第一忘记ELSE和NULL的聚合语义。问SUM(CASE WHEN type A THEN amount END)和SUM(CASE WHEN type A THEN amount ELSE 0 END)有什么区别答案是前者未命中的行累计的是NULLSUM不计算NULL结果相当于把所有未命中的行从总和中删掉了后者未命中记0对SUM结果没有影响。但如果把SUM换成COUNT两者差异会被放大前者COUNT统计的是命中的行数后者COUNT会把所有未命中行也数进去因为0不是NULL。同样是CASE WHEN配SUM和配COUNT的语义完全不同代码review时一定要看清。第二简单CASE和NULL判断。问CASE col WHEN NULL THEN 空 ELSE 非空 END结果是什么答案是永远返回‘非空’。原因前面说过NULL NULL结果是UNKNOWN。必须用CASE WHEN col IS NULL。第三THEN分支返回值类型不一致。比如一个分支返回字符串另一个分支返回数字CASE WHEN flag 1 THEN 是 ELSE 0 END在MySQL里会触发隐式类型转换可能把是转成0也可能把0转成0结果完全看上下文极难排查。在PostgreSQL这类强类型数据库里直接报错。所以写的时候要保证所有THEN分支外加ELSE返回值类型一致字符串就都是字符串数字就都是数字。4.4 同族函数对比IF、DECODE、COALESCE、NULLIFCASE WHEN不是唯一的条件判断函数不同数据库有自己的方言。面试里被问到“你为什么用CASE WHEN而不用IF”时可以这样回答CASE WHEN是ANSI SQL标准的一部分在MySQL、PostgreSQL、SQL Server、Oracle、SQLite等几乎所有数据库里都通用而IF、DECODE这类写法是各家的私有扩展换库就要改代码。简单归纳一下数据库条件判断能力备注MySQLCASE WHEN、IF(expr, v1, v2)、IFNULLIF适合简单二选一复杂条件一律CASESQL ServerCASE WHEN、IIF(expr, v1, v2)IIF从2012版本开始提供OracleCASE WHEN、DECODEDECODE只能做等值比较CASE更灵活PostgreSQLCASE WHEN、COALESCE、NULLIFCOALESCE取第一个非NULL值NULLIF判断两值是否相等我的意见很明确新写的SQL里条件判断统一用CASE WHEN。只有遇到极简单的“字段为空给默认值”这种场景才考虑COALESCE或IFNULL因为它们更短、语义更清晰。5. 实战避坑手册这些坑我替你踩过了5.1 常见错误写法对照表这节直接给你一张自查表都是我在实际项目里见过或者自己踩过的坑错误写法错误原因正确写法CASE WHEN score 90 THEN 优秀 WHEN score 60 THEN 及格条件顺序颠倒顺序求解导致90分先命中‘及格’从严格到宽松先90再60CASE col WHEN NULL THEN 空 ENDNULL NULL结果为UNKNOWNCASE WHEN col IS NULL THEN 空 ENDSUM(CASE WHEN typeA THEN amount END)未命中计入NULLSUM丢弃报表少数据SUM(CASE WHEN typeA THEN amount ELSE 0 END)COUNT(CASE WHEN typeA THEN 1 ELSE 0 END)ELSE 0导致COUNT把所有行都数进COUNT(CASE WHEN typeA THEN 1 END)CASE WHEN flag1 THEN 是 ELSE 0 END返回类型不一致隐式转换或直接报错统一返回类型CASE status WHEN 1 THEN 待处理 WHEN 2 THEN 处理中 END在END后漏写别名结果集列名显示为空或乱码END AS status_nameWHERE条件里写CASE WHEN a1 THEN col1 ELSE col2 END 10索引失效全表扫描改写为(a1 AND col110) OR (a1 AND col210)这张表值得你在写完SQL之后逐条对照一遍。特别是前四行都属于“不报错但结果悄悄错”的类型最坑。5.2 GROUP BY里写CASE表达式的两种处理方式前面说过GROUP BY里不能直接用SELECT别名所以会出现一整段重复的CASE表达式既丑又容易改漏。我在实际开发里常用两种办法规避。第一种是用子查询或CTE把CASE计算封装一层WITH user_tags AS ( SELECT user_id, CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 30 THEN 青年 ELSE 中年及以上 END AS age_group FROM users ) SELECT age_group, COUNT(*) FROM user_tags GROUP BY age_group第二种办法是用计算字段的序号MySQL和部分数据库支持按SELECT中的位置分组SELECT CASE WHEN age 18 THEN 未成年 ELSE 成年 END AS age_group, COUNT(*) FROM users GROUP BY 1GROUP BY 1表示按SELECT第一个字段分组。这种写法简洁但可读性比较差而且一旦字段顺序调整就会改变分组逻辑我一般不推荐在很长的SQL里用。真要说工程上最稳的方案还是CTE封装逻辑清晰、可维护性强、跨数据库兼容性也好。5.3 各数据库方言的兼容性提醒CASE WHEN本身是标准SQL但不同数据库对它的处理细节有差异跨库迁移前最好知道这些坑。MySQL对类型的容忍度最高CASE表达式分支类型不一致时经常做隐式转换不报错但结果可能不符合预期。如果你在MySQL里遇到CASE WHEN ... THEN 值 ELSE 数字 END这种写法结果是字符串还是数字完全取决于上下文别赌统一类型。SQL Server对CASE表达式的类型强制比MySQL严格分支类型不一致时会尝试把低优先级类型转换到高优先级类型转换不成功就直接报错。另外SQL Server的CASE表达式只是标量表达式不能用在存储过程的流程控制里控制流是IF...ELSE和WHILE的事别搞混。PostgreSQL是类型最严格的数据库之一。THEN分支类型不一致直接跑错你必须用CAST显式转换。虽然写起来啰嗦但PostgreSQL这种严格其实能帮你提前发现很多其他数据库里悄悄埋下的类型Bug。Oracle老项目里大量使用DECODE它的局限在于只能做等值比较不能写范围条件而且可读性不如CASE。新代码建议直接CASE WHEN迁移成本也更低。5.4 排查CASE WHEN相关慢SQL的经验最后分享一个我实际处理过的案例。某个报表SQL每天凌晨跑要跑将近二十分钟业务方忍无可忍来让我看。打开SQL我发现里面有一个大表SELECT字段里有一个CASE WHEN它的THEN分支各嵌了一段子查询SELECT order_id, CASE WHEN o.is_urgent 1 THEN (SELECT MAX(create_time) FROM logistics WHERE order_id o.order_id) ELSE o.create_time END AS effective_time FROM orders o WHERE o.create_time 2024-01-01这段子查询在is_urgent为1的每一行上执行一次几百万行配几百万次子查询不慢才怪。我的处理是把物流表按order_id先聚合一次再LEFT JOIN出来SELECT o.order_id, CASE WHEN o.is_urgent 1 THEN l.max_create_time ELSE o.create_time END AS effective_time FROM orders o LEFT JOIN ( SELECT order_id, MAX(create_time) AS max_create_time FROM logistics GROUP BY order_id ) l ON l.order_id o.order_id WHERE o.create_time 2024-01-01改完之后整个报表从二十分钟降到了几十秒。核心结论很简单CASE WHEN只是行内的逻辑映射它本身不是性能瓶颈真正的瓶颈是CASE分支里藏着的逐行计算比如标量子查询、函数调用、隐式类型转换。遇到CASE WHEN相关的慢SQL先检查THEN分支里有没有藏着子查询再看WHERE里有没有对索引列用CASE包裹这两类属于最高频的根因。至于怎么定位没有捷径老老实实跑EXPLAIN看执行计划。我见过不少人用慢日志抓到SQL之后直接拍脑袋改结果越改越慢。EXPLAIN里的rows估算值是很好的参考对比改写前后的扫描行数你就知道优化有没有效。我自己写CASE WHEN这些年最大的心得是它本质上不是什么高深技术但非常能反映一个人有没有从“能查到”走到“查得好”。同样一个报表需求新手用三条子查询拼老手一条CASE WHEN写完跑起来速度还快一个量级。建议你在自己的项目里做一次“CASE WHEN重构练习”把一个用多个子查询或多次查询拼出来的统计改写成单条条件聚合SQL再对比执行计划里的扫描行数那个差距会让你印象非常深刻。SQL优化很多时候不需要什么玄学老老实实把CASE WHEN用对就已经超过了大部分人。 CASE WHEN用法详解：从执行逻辑到SQL性能优化的实战指南
写SQL的时间久了你会发现一个挺有意思的现象真正能拉开人与人差距的往往不是那些看起来炫技的窗口函数或几十层嵌套子查询而是一个基础到容易被忽略的关键字——CASE WHEN。它在SQL里的角色有点像炒菜时的盐单拎出来没人会多看一眼但整道菜好不好吃盐放没放到位置差别是决定性的。它可以用来做简单的字段打标更是行转列、条件聚合、分段统计、数据去重这些高频场景里的主力工具。这篇文章会把CASE WHEN的执行逻辑、实战用法、性能陷阱、面试考点一次讲透新手可以直接照着抄老手也能借这个机会查漏补缺。我这两年帮团队review了不少SQL发现大家用CASE WHEN的水平分化很严重。一拨人只会在SELECT后面加一个最简单的判断打标签碰到稍微复杂的业务就疯狂堆子查询把自己绕晕也让数据库被迫一遍遍扫表。另一拨人把CASE WHEN和聚合、窗口函数、UPDATE结合起来用一条SQL就把行转列、分组统计、优先级排序全干完了代码又短又清晰。这篇文章致力于让你成为第二拨人下面直接进正题。1. 先把CASE WHEN的底裤翻出来语法和执行逻辑1.1 两种写法别用混了CASE WHEN在SQL里一共有两种语法形态虽然绝大多数教材把它们放在一起讲但理解两者的区别非常重要面试和工作里都容易在这里栽跟头。第一种叫简单CASE表达式长得像自动售货机CASE column_name WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE resultN END它的工作方式就是拿着column_name的值逐个和WHEN后面的值做等值比较。注意只能做等值比较。也就是这种判断。第二种叫搜索CASE表达式长得像人工服务柜台CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE resultN END这种写法不限定列名WHEN后面可以放任何结果为真假的表达式。所以、、LIKE、BETWEEN、IN、IS NULL甚至一个EXISTS子查询全都能往里塞。我见过不少人在需要做范围判断的时候还在用简单CASE硬凑写出一堆WHEN score 90这种条件实际上应该用的是搜索CASE的WHEN score 90。记住这个区分别管等值判断用什么一涉及范围、模糊、空值判断请直接切到搜索CASE表达式。1.2 执行顺序是自上而下的别把条件写反CASE WHEN是顺序求解的数据库会从第一个WHEN开始往下判断哪个条件先为真就返回对应的THEN后面的条件直接不再执行。很多人写分段统计时在这里踩坑比如CASE WHEN score 60 THEN 及格 WHEN score 90 THEN 优秀 ELSE 不及格 END这条语句跑出来所有90分以上的学生都会被打成“及格”。因为90分也满足score 60数据库从头扫到第一个为真的条件就返回了“优秀”这个分支永远等不到。写分段逻辑时必须把最严格的条件放最前面CASE WHEN score 90 THEN 优秀 WHEN score 60 THEN 及格 ELSE 不及格 END这个顺序问题不止出现在CASE WHEN里但CASE WHEN最容易让人疏忽因为它看起来太简单了。我的习惯是写任何分段或优先级判断先停下来想清楚条件集合是不是互斥的。如果互斥顺序随意如果存在包含关系就按从窄到宽排。1.3 NULL和ELSE的两处大坑第一处坑是简单CASE表达式的NULL判断。下面这个写法看起来没毛病实际上永远走不到第一个分支CASE column_name WHEN NULL THEN 空值 ELSE 非空 END原因很简单SQL里NULL NULL的结果不是真也不是假而是UNKNOWN。自动售货机模式做的等值判断拿NULL去匹配NULL根本匹配不上。想判断空值必须用搜索CASECASE WHEN column_name IS NULL THEN 空值 ELSE 非空 END第二处坑是ELSE缺失。省略ELSE时所有未匹配的行会返回NULL。很多聚合场景下这个NULL会被静默丢弃。我举个例子统计线上和线下支付金额SELECT user_id, SUM(CASE WHEN pay_type online THEN amount END) AS online_amount, SUM(CASE WHEN pay_type offline THEN amount END) AS offline_amount FROM orders GROUP BY user_id这段SQL看起来没问题但实际跑出来某个用户如果全部是线上支付他的offline_amount会是NULL而不是0。表面看只是结果集里多了个空值但报表工具、Java代码、Python处理NULL的方式各不相同轻则页面显示空白重则程序抛空指针。加上ELSE 0就能把SUM的语义变成“没匹配到的记0”这是条件聚合里最常用也最该养成习惯的写法。2. 高频实战CASE WHEN到底天天在干什么活2.1 条件聚合与行转列一次扫描取代N次子查询CASE WHEN在实战中最常见的搭档是聚合函数。核心思路是用CASE WHEN把每一行“归类”并映射成目标值再用SUM、COUNT、MAX等聚合函数把同类数据压成一个标量。举个经典的行转列例子。订单表结构大致是-- 字段user_id, order_id, amount, statuspaid/refund/pending现在要统计每个用户的已支付总金额、退款总金额、待支付总金额一列一个字段。常见的新手写法是三个子查询硬拼SELECT user_id, (SELECT SUM(amount) FROM orders WHERE user_id a.user_id AND status paid) AS paid_amount, (SELECT SUM(amount) FROM orders WHERE user_id a.user_id AND status refund) AS refund_amount FROM orders a GROUP BY user_id这种写法最大的问题是性能。orders表每扫描出一个用户就要为这三个子查询各跑一次全表扫描假设没有索引或索引选择性不佳数据量一大必卡。CASE WHEN的写法是SELECT user_id, SUM(CASE WHEN status paid THEN amount ELSE 0 END) AS paid_amount, SUM(CASE WHEN status refund THEN amount ELSE 0 END) AS refund_amount, SUM(CASE WHEN status pending THEN amount ELSE 0 END) AS pending_amount FROM orders GROUP BY user_id这条SQL全程只扫一遍orders表每一行经过三个CASE判断分别落入对应的累加器一次物理扫描同时完成三个维度的汇总。原理上就是经典的“行转列”思路把行里的分类值变成结果集里的列。同样是统计执行计划差着量级这就是为什么我说CASE WHEN看着基础用好了直接决定SQL的下限。2.2 分段统计给数据打上业务标签再分组条件聚合之外的另一个高频场景是分段。比如用户表里有年龄字段想按年龄段统计人数常规做法是SELECT CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 30 THEN 青年 WHEN age BETWEEN 31 AND 50 THEN 中年 ELSE 老年 END AS age_group, COUNT(*) FROM users GROUP BY CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 30 THEN 青年 WHEN age BETWEEN 31 AND 50 THEN 中年 ELSE 老年 END我知道你一定想问SELECT里已经写了CASE表达式还起了别名age_group为什么GROUP BY不能直接写GROUP BY age_group原因出在SQL的逻辑执行顺序上。数据库实际执行的流程大致是先FROM取数据再WHERE过滤再GROUP BY分组然后才是SELECT计算字段。SELECT别名是在分组之后才生成的GROUP BY阶段根本还没这个别名。所以要用同样的CASE表达式再写一遍。当然日常开发里为了避免一大坨重复的CASE表达式更常见的做法是先用子查询或CTE把分段标签算好再在外层分组SELECT age_group, COUNT(*) FROM ( SELECT CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 30 THEN 青年 ELSE 中年及以上 END AS age_group FROM users ) t GROUP BY age_group这两种写法各有适用场景。前者适合CASE逻辑简单、只在这一个地方用到的情况后者适合CASE逻辑复杂、而且后续可能还要基于这个标签继续做关联的情况。唯一需要提醒的是MySQL在宽松的sql_mode下确实允许SELECT别名直接用在GROUP BY里但它本质上是在玩火换到别的数据库或者换一组sql_mode配置SQL立刻报错。我建议按标准写法来跨库迁移时省心。2.3 数据清洗与分组去重和ROW_NUMBER搭档效果极佳很多时候报表数据不是干净的。比如用户表里的邮箱字段有的填QQ邮箱有的填163有的填企业邮箱。要按邮箱类型统计用户分布就可以用CASE WHEN做归一化SELECT CASE WHEN email LIKE %qq.com THEN QQ邮箱 WHEN email LIKE %163.com THEN 网易邮箱 WHEN email LIKE %gmail.com THEN 谷歌邮箱 ELSE 其他 END AS email_type, COUNT(DISTINCT user_id) AS user_cnt FROM users GROUP BY CASE WHEN email LIKE %qq.com THEN QQ邮箱 WHEN email LIKE %163.com THEN 网易邮箱 WHEN email LIKE %gmail.com THEN 谷歌邮箱 ELSE 其他 END这里的COUNT(DISTINCT user_id)就带上了去重功能和CASE WHEN配合能在一次分组里完成“按规则归类和去重计数”两件事。如果去重的逻辑更复杂比如订单明细表存在重复导入每个用户的订单可能出现多条几乎相同的记录现在要保留每个用户最新一条有效订单就轮到CASE WHEN和窗口函数搭档了SELECT user_id, order_id, amount FROM ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER ( PARTITION BY user_id, order_no ORDER BY create_time DESC ) AS rn FROM orders ) t WHERE rn 1窗口函数负责为每个分组内的记录排号外层WHERE只取编号为1的记录。这套组合拳是“分组后去重取最新”的标配写法CASE WHEN在里面的作用更多是配合聚合或者过滤条件比如只统计类型为有效的订单SELECT user_id, order_id, amount FROM ( SELECT user_id, order_id, amount, ROW_NUMBER() OVER ( PARTITION BY user_id, order_no ORDER BY create_time DESC ) AS rn, CASE WHEN status valid THEN 1 ELSE 0 END AS valid_flag FROM orders ) t WHERE rn 1 AND valid_flag 12.4 在UPDATE里做批量条件更新一条语句处理所有分支CASE WHEN不只出现在SELECT里它在UPDATE语句里的价值被严重低估。假设商品表要按库存状态调价库存为0的商品涨价10%库存低于10的商品涨价5%其余不涨价。常规做法是三条UPDATE各自跑一遍UPDATE products SET price price * 1.10 WHERE stock 0; UPDATE products SET price price * 1.05 WHERE stock 10 AND stock 0;这三条语句分开执行的问题在于每一条都要单独开启事务、写一遍日志、加一遍锁更重要的是如果某个商品同时满足多个条件比如先执行了第一条涨价10%再执行第二条又涨价5%价格就被复利式地涨了两轮。而业务本意是只按一个规则调整。用CASE WHEN一条UPDATE搞定UPDATE products SET price CASE WHEN stock 0 THEN price * 1.10 WHEN stock 10 THEN price * 1.05 ELSE price END这里有个细节值得注意CASE WHEN是顺序求解的所以价格不会重复累加。而且整条UPDATE是单语句数据库只需要写一次日志、持有一轮锁代码在执行层也不会有“多条语句之间的中间状态”问题。热词里有人搜“sql server writelog”其实就是在关心这类写操作产生的日志量。把多条UPDATE合并成一条带CASE WHEN的UPDATE是减少日志写入、改善并发性能的实践之一。当然如果本来就是三条独立业务规则需要分别记录变更历史那就别硬并是不是该合并要结合业务来judge。3. 进阶技巧把CASE WHEN用出花同时别把性能拖垮3.1 自定义排序ORDER BY里写CASE控制展示顺序数据库默认的排序只有升序降序数字按大小字符串按字典序。但业务上经常有“不按字典序排”的需求。比如订单状态要按“待处理、处理中、已完成、已取消”这个业务顺序展示而不是按拼音或字母顺序。这时候CASE WHEN就派上用场了SELECT order_id, status FROM orders ORDER BY CASE status WHEN pending THEN 1 WHEN processing THEN 2 WHEN completed THEN 3 WHEN cancelled THEN 4 ELSE 5 END原理不复杂待处理映射成1处理中映射成2数据库按映射后的数字升序排就得到了业务想要的顺序。这个技巧在报表里特别实用尤其是“合计”这一行如果想让它在最后一行展示也可以先把合计映射成一个比较大的排序值。而且ORDER BY里的CASE不会影响WHERE的执行计划在大结果集上排序虽然会占用内存或临时文件但这是排序本身的开销跟CASE表达式关系不大。3.2 与窗口函数组合条件计数不求人窗口函数是这两年SQL面试的高频考点CASE WHEN和它配合最经典的场景是“按条件计数”。比如要统计每个用户的高价值订单数量金额大于100的算高价值直觉反应可能是COUNT(amount 100)但COUNT函数只接受字段名或*不支持布尔表达式。标准做法就是用CASE WHEN把条件变成1或0再交给SUMSELECT user_id, SUM(CASE WHEN amount 100 THEN 1 ELSE 0 END) AS high_value_cnt, COUNT(*) AS total_cnt FROM orders GROUP BY user_id如果把上面的普通聚合换成窗口函数场景就变成在保留每一行订单明细的同时给每行附加“该用户总共有几笔高价值订单”这个汇总信息。这就是CASE WHEN加窗口函数的正确用法SELECT order_id, user_id, amount, SUM(CASE WHEN amount 100 THEN 1 ELSE 0 END) OVER (PARTITION BY user_id) AS user_high_value_cnt FROM orders注意OVER (PARTITION BY user_id)这个子句让SUM变成了窗口聚合它不会压缩行数而是把计算结果复制到每一行。CASE WHEN在这里的作用是“条件开关”命中条件贡献1不命中贡献0SUM加总后就是符合条件的数量。这个套路你掌握之后很多“既要明细又要汇总”的报表都能用一条SQL写出来不用再费劲JOIN一个临时聚合表。3.3 性能陷阱小心CASE WHEN帮倒忙废掉索引CASE WHEN用顺手之后容易产生一种“什么都想用CASE”的冲动但有几类写法会导致性能灾难我一个个说。第一类在WHERE条件里用CASE WHEN包住索引列。比如SELECT * FROM orders WHERE CASE WHEN status paid THEN create_time ELSE update_time END 2024-01-01这种写法从语义上讲不同的行会拿不同的字段去比较优化器无法把它转换成对create_time或update_time的范围扫描只能对全表逐行计算CASE再判断索引直接失效。遇到类似需求化整为零反而更快SELECT * FROM orders WHERE (status paid AND create_time 2024-01-01) OR (status paid AND update_time 2024-01-01)这段改写虽然出现了OR但两个分支分别可以走对应的索引取决于数据分布优化器也可能选择分别索引扫描再合并。不过在数据分布极度不均时这种OR写法也可能不如UNION ALL这个要看执行计划说话不能拍脑袋。第二类在CASE的THEN子句里放标量子查询。SELECT CASE WHEN type A THEN (SELECT MAX(price) FROM products WHERE category_id o.category_id) ELSE 999 END AS price FROM orders o如果orders表有十万行这个THEN里的子查询在极端情况下会被执行十万次有没有物化取决于优化器通常是不乐观的。遇到这类需求优先把子查询改成提前JOINSELECT CASE WHEN o.type A THEN t.max_price ELSE 999 END AS price FROM orders o LEFT JOIN ( SELECT category_id, MAX(price) AS max_price FROM products GROUP BY category_id ) t ON t.category_id o.category_id把计算提前到一次聚合里CASE WHEN只做最后的映射两者性能天差地别。3.4 慢SQL优化里的正确姿势合并扫描、减少IO热词里有“慢sql优化”这也是CASE WHEN特别能发光发热的领域。最常见的慢SQL场景是同一张表、同一种维度只是条件不同就写成三条查询再用UNION或应用层代码拼结果。比如统计今天不同类型订单的数量SELECT COUNT(*) FROM orders WHERE status paid AND create_time 2024-01-01; SELECT COUNT(*) FROM orders WHERE status refund AND create_time 2024-01-01; SELECT COUNT(*) FROM orders WHERE status pending AND create_time 2024-01-01;这三条SQL各扫一遍符合条件的行物理上就是把同一批数据读了三次。改成CASE WHEN条件聚合SELECT COUNT(CASE WHEN status paid THEN 1 END) AS paid_cnt, COUNT(CASE WHEN status refund THEN 1 END) AS refund_cnt, COUNT(CASE WHEN status pending THEN 1 END) AS pending_cnt FROM orders WHERE create_time 2024-01-01注意这里用了COUNT(CASE WHEN ... THEN 1 END)因为COUNT只统计非NULL所以没命中的行自动被忽略不需要写ELSE 0。这样一次扫描同时数出三类数量IO直接减少到三分之一。但过犹不及。如果这三次查询各自的过滤条件过滤性差别极大比如paid的订单有千万级、refund只有几十条那么分开查询配合索引可能比合并扫描更快。写SQL没有银弹CASE WHEN合并SQL只是手段真正要做的是看执行计划里的扫描行数。我见过不少人把能拆的SQL强行合并成一个巨无霸CASE结果优化器走错执行计划比原来更慢这不是CASE WHEN的锅是过度优化的锅。4. 面试现场CASE WHEN的加分答法4.1 高频面试题学生成绩行转列CASE WHEN在面试题里出境频率最高的就是行转列。题目通常是这样的有一张成绩表student_idcoursescore1语文901数学851英语882语文752数学92要求查成student_id语文数学英语190858827592NULL标准答案SELECT student_id, MAX(CASE WHEN course 语文 THEN score END) AS chinese_score, MAX(CASE WHEN course 数学 THEN score END) AS math_score, MAX(CASE WHEN course 英语 THEN score END) AS english_score FROM score GROUP BY student_id这里很多初学者会问为什么用MAX取最大值不是把其他成绩丢掉了吗实际上在这个场景里同一学生同一科目分组后只有一个值MAX不是“取最大”而是“从一组里挑出那个非NULL的值”。GROUP BY后每组只有一行scoreCASE负责把不属于当前科目的score置为NULLMAX自然就选到了剩下的那个值。这个题的关键不是MAX函数本身而是理解GROUP BY加CASE WHEN的“填表”过程。同理也可以用MIN。4.2 加分的深入点如果同一科目有多条记录怎么办面试官往往会在你答完基础版本后追问如果同一个学生同一个科目有多条考试成绩我只想取最近一次的分数怎么写这就是进阶考点。要分两步先用窗口函数给每个学生每门课按考试时间排序再在排序结果基础上做行转列SELECT student_id, MAX(CASE WHEN course 语文 THEN score END) AS chinese_score, MAX(CASE WHEN course 数学 THEN score END) AS math_score FROM ( SELECT student_id, course, score, ROW_NUMBER() OVER ( PARTITION BY student_id, course ORDER BY exam_time DESC ) AS rn FROM score ) t WHERE rn 1 GROUP BY student_id这个写法把“每组取最新一条”的问题拆成两步窗口函数负责排序和筛选外层CASE WHEN负责旋转。能主动讲出这一步面试官对你的评价会明显不一样因为这证明你不是只会背模板而是理解了两个基础能力窗口函数的行内排名、CASE WHEN的列内映射如何组合。4.3 容易翻车的三个点能答对就是加分项面试里考察CASE WHEN很多时候不是让你写一段复杂的SQL而是让你挑错。我总结三个高频翻车点。第一忘记ELSE和NULL的聚合语义。问SUM(CASE WHEN type A THEN amount END)和SUM(CASE WHEN type A THEN amount ELSE 0 END)有什么区别答案是前者未命中的行累计的是NULLSUM不计算NULL结果相当于把所有未命中的行从总和中删掉了后者未命中记0对SUM结果没有影响。但如果把SUM换成COUNT两者差异会被放大前者COUNT统计的是命中的行数后者COUNT会把所有未命中行也数进去因为0不是NULL。同样是CASE WHEN配SUM和配COUNT的语义完全不同代码review时一定要看清。第二简单CASE和NULL判断。问CASE col WHEN NULL THEN 空 ELSE 非空 END结果是什么答案是永远返回‘非空’。原因前面说过NULL NULL结果是UNKNOWN。必须用CASE WHEN col IS NULL。第三THEN分支返回值类型不一致。比如一个分支返回字符串另一个分支返回数字CASE WHEN flag 1 THEN 是 ELSE 0 END在MySQL里会触发隐式类型转换可能把是转成0也可能把0转成0结果完全看上下文极难排查。在PostgreSQL这类强类型数据库里直接报错。所以写的时候要保证所有THEN分支外加ELSE返回值类型一致字符串就都是字符串数字就都是数字。4.4 同族函数对比IF、DECODE、COALESCE、NULLIFCASE WHEN不是唯一的条件判断函数不同数据库有自己的方言。面试里被问到“你为什么用CASE WHEN而不用IF”时可以这样回答CASE WHEN是ANSI SQL标准的一部分在MySQL、PostgreSQL、SQL Server、Oracle、SQLite等几乎所有数据库里都通用而IF、DECODE这类写法是各家的私有扩展换库就要改代码。简单归纳一下数据库条件判断能力备注MySQLCASE WHEN、IF(expr, v1, v2)、IFNULLIF适合简单二选一复杂条件一律CASESQL ServerCASE WHEN、IIF(expr, v1, v2)IIF从2012版本开始提供OracleCASE WHEN、DECODEDECODE只能做等值比较CASE更灵活PostgreSQLCASE WHEN、COALESCE、NULLIFCOALESCE取第一个非NULL值NULLIF判断两值是否相等我的意见很明确新写的SQL里条件判断统一用CASE WHEN。只有遇到极简单的“字段为空给默认值”这种场景才考虑COALESCE或IFNULL因为它们更短、语义更清晰。5. 实战避坑手册这些坑我替你踩过了5.1 常见错误写法对照表这节直接给你一张自查表都是我在实际项目里见过或者自己踩过的坑错误写法错误原因正确写法CASE WHEN score 90 THEN 优秀 WHEN score 60 THEN 及格条件顺序颠倒顺序求解导致90分先命中‘及格’从严格到宽松先90再60CASE col WHEN NULL THEN 空 ENDNULL NULL结果为UNKNOWNCASE WHEN col IS NULL THEN 空 ENDSUM(CASE WHEN typeA THEN amount END)未命中计入NULLSUM丢弃报表少数据SUM(CASE WHEN typeA THEN amount ELSE 0 END)COUNT(CASE WHEN typeA THEN 1 ELSE 0 END)ELSE 0导致COUNT把所有行都数进COUNT(CASE WHEN typeA THEN 1 END)CASE WHEN flag1 THEN 是 ELSE 0 END返回类型不一致隐式转换或直接报错统一返回类型CASE status WHEN 1 THEN 待处理 WHEN 2 THEN 处理中 END在END后漏写别名结果集列名显示为空或乱码END AS status_nameWHERE条件里写CASE WHEN a1 THEN col1 ELSE col2 END 10索引失效全表扫描改写为(a1 AND col110) OR (a1 AND col210)这张表值得你在写完SQL之后逐条对照一遍。特别是前四行都属于“不报错但结果悄悄错”的类型最坑。5.2 GROUP BY里写CASE表达式的两种处理方式前面说过GROUP BY里不能直接用SELECT别名所以会出现一整段重复的CASE表达式既丑又容易改漏。我在实际开发里常用两种办法规避。第一种是用子查询或CTE把CASE计算封装一层WITH user_tags AS ( SELECT user_id, CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 30 THEN 青年 ELSE 中年及以上 END AS age_group FROM users ) SELECT age_group, COUNT(*) FROM user_tags GROUP BY age_group第二种办法是用计算字段的序号MySQL和部分数据库支持按SELECT中的位置分组SELECT CASE WHEN age 18 THEN 未成年 ELSE 成年 END AS age_group, COUNT(*) FROM users GROUP BY 1GROUP BY 1表示按SELECT第一个字段分组。这种写法简洁但可读性比较差而且一旦字段顺序调整就会改变分组逻辑我一般不推荐在很长的SQL里用。真要说工程上最稳的方案还是CTE封装逻辑清晰、可维护性强、跨数据库兼容性也好。5.3 各数据库方言的兼容性提醒CASE WHEN本身是标准SQL但不同数据库对它的处理细节有差异跨库迁移前最好知道这些坑。MySQL对类型的容忍度最高CASE表达式分支类型不一致时经常做隐式转换不报错但结果可能不符合预期。如果你在MySQL里遇到CASE WHEN ... THEN 值 ELSE 数字 END这种写法结果是字符串还是数字完全取决于上下文别赌统一类型。SQL Server对CASE表达式的类型强制比MySQL严格分支类型不一致时会尝试把低优先级类型转换到高优先级类型转换不成功就直接报错。另外SQL Server的CASE表达式只是标量表达式不能用在存储过程的流程控制里控制流是IF...ELSE和WHILE的事别搞混。PostgreSQL是类型最严格的数据库之一。THEN分支类型不一致直接跑错你必须用CAST显式转换。虽然写起来啰嗦但PostgreSQL这种严格其实能帮你提前发现很多其他数据库里悄悄埋下的类型Bug。Oracle老项目里大量使用DECODE它的局限在于只能做等值比较不能写范围条件而且可读性不如CASE。新代码建议直接CASE WHEN迁移成本也更低。5.4 排查CASE WHEN相关慢SQL的经验最后分享一个我实际处理过的案例。某个报表SQL每天凌晨跑要跑将近二十分钟业务方忍无可忍来让我看。打开SQL我发现里面有一个大表SELECT字段里有一个CASE WHEN它的THEN分支各嵌了一段子查询SELECT order_id, CASE WHEN o.is_urgent 1 THEN (SELECT MAX(create_time) FROM logistics WHERE order_id o.order_id) ELSE o.create_time END AS effective_time FROM orders o WHERE o.create_time 2024-01-01这段子查询在is_urgent为1的每一行上执行一次几百万行配几百万次子查询不慢才怪。我的处理是把物流表按order_id先聚合一次再LEFT JOIN出来SELECT o.order_id, CASE WHEN o.is_urgent 1 THEN l.max_create_time ELSE o.create_time END AS effective_time FROM orders o LEFT JOIN ( SELECT order_id, MAX(create_time) AS max_create_time FROM logistics GROUP BY order_id ) l ON l.order_id o.order_id WHERE o.create_time 2024-01-01改完之后整个报表从二十分钟降到了几十秒。核心结论很简单CASE WHEN只是行内的逻辑映射它本身不是性能瓶颈真正的瓶颈是CASE分支里藏着的逐行计算比如标量子查询、函数调用、隐式类型转换。遇到CASE WHEN相关的慢SQL先检查THEN分支里有没有藏着子查询再看WHERE里有没有对索引列用CASE包裹这两类属于最高频的根因。至于怎么定位没有捷径老老实实跑EXPLAIN看执行计划。我见过不少人用慢日志抓到SQL之后直接拍脑袋改结果越改越慢。EXPLAIN里的rows估算值是很好的参考对比改写前后的扫描行数你就知道优化有没有效。我自己写CASE WHEN这些年最大的心得是它本质上不是什么高深技术但非常能反映一个人有没有从“能查到”走到“查得好”。同样一个报表需求新手用三条子查询拼老手一条CASE WHEN写完跑起来速度还快一个量级。建议你在自己的项目里做一次“CASE WHEN重构练习”把一个用多个子查询或多次查询拼出来的统计改写成单条条件聚合SQL再对比执行计划里的扫描行数那个差距会让你印象非常深刻。SQL优化很多时候不需要什么玄学老老实实把CASE WHEN用对就已经超过了大部分人。