1. 核心语法拆解先搞清楚CASE WHEN的执行逻辑再动手1.1 两种写法的适用差异PostgreSQL里的CASE WHEN有两种形态很多初学者一开始容易混在一起。第一种叫简单CASE写法是CASE后面跟着一个表达式WHEN后面是值SELECT status, CASE status WHEN 0 THEN 待处理 WHEN 1 THEN 已受理 WHEN 2 THEN 已办结 ELSE 未知状态 END AS status_text FROM work_order;第二种叫搜索CASEWHEN后面直接跟完整的条件表达式这也是实际中使用频率最高的SELECT duration, CASE WHEN duration 60 THEN 不足1小时 WHEN duration 480 THEN 1小时内 WHEN duration 1440 THEN 1天内 ELSE 超过1天 END AS duration_level FROM task_record;简单CASE适合判断一个字段的离散值写法更紧凑搜索CASE适合范围判断、多字段联合判断这类场景。两种写法的执行逻辑本质相同只是一个用等值比较一个用完整条件表达式。判断顺序上CASE语句是自上而下逐条匹配的只要第一条命中了就立刻返回结果后面的分支不再参与判断。这意味着你把更具体的条件放在前面、更宽泛的条件放在后面结果才是符合预期的。搜索CASE尤其要注意这一点因为条件的先后直接决定结果归属。1.2 ELSE分支到底要不要写先说结论除非你非常确定所有分支都能被覆盖否则建议保留ELSE。CASE语句在没有ELSE的情况下如果所有WHEN都不命中返回值是NULL。这个NULL很容易引发连锁问题比如你把它做加减乘除、拼进字符串、或者JOIN到别的表都会出现让人摸不着头脑的结果。有一次我处理一批订单数据时需要给订单打上“大额”“普通”的标签条件就两个金额大于等于10000标“大额”小于10000标“普通”。看起来全覆盖了于是有同事省略了ELSE。结果后面数据里混进了一批金额为NULL的订单这些订单全部变成了NULL标签后续统计里这批订单就“消失”了。排查了很久才发现根源就是NULL值的订单没有被任何WHEN捕获ELSE的缺失让NULL悄悄蔓延到了下游。所以我的建议是没有十足的把握就写ELSE实在不想写也至少保证有兜底逻辑。尤其是处理外部分析系统、数据接口传过来的数据时字段里出现NULL是常态不明来源的异常值是常态ELSE这个兜底是最后一道防线。1.3 一个容易被忽略的细节WHEN分支的表达式求值顺序CASE的短路行为还体现在表达式求值上。一旦某个WHEN条件满足PostgreSQL会直接返回对应的THEN结果后续分支里的表达式不会再被计算。这可以用来规避一些潜在的错误。比如你想要计算一个除法但被除数可能为0你可以这么写SELECT CASE WHEN total_count 0 THEN 0 ELSE success_count / total_count END AS success_rate FROM biz_stats;这里当total_count为0时直接返回0不会进入ELSE部分自然也不会触发除以0的错误。这个技巧在报表统计中使用频率很高。凡是可能出错的表达式都优先放在靠后的分支里让它在条件被跳过时不参与计算。另外CASE表达式支持多个THEN返回不同类型但实际使用中要控制住。返回类型不一致时PostgreSQL会尝试做隐式转换转换不了就直接报错。比如一个分支返回字符串另一个分支返回数字这种写法在绝大多数情况下是不被允许的即使建表时没报错查询执行时也会报错。保持分支返回值类型一致是最稳妥的做法。2. 高频应用场景从统计聚合到行转列一套写法吃透2.1 用CASE WHEN做条件聚合一张表统计多个维度CASE WHEN在聚合函数里的用法是我个人觉得性价比最高的技巧。常见场景是一张明细表你需要同时统计多个业务状态的数量、金额或者按不同口径拆成绩效看板。普通思路是多次查询再合并实际上一条SQL配合条件聚合就可以搞定SELECT department_id, COUNT(*) AS total_order_count, COUNT(*) FILTER (WHERE status 0) AS pending_count, SUM(CASE WHEN amount 10000 THEN 1 ELSE 0 END) AS large_amount_count, SUM(CASE WHEN pay_time IS NOT NULL THEN 1 ELSE 0 END) AS paid_count, COALESCE(SUM(CASE WHEN status 2 THEN amount ELSE 0 END), 0) AS completed_amount FROM order_list WHERE create_time now() - interval 7 days GROUP BY department_id ORDER BY total_order_count DESC;这里的技巧是SUM(CASE WHEN ... THEN 1 ELSE 0 END)相当于“计数满足条件的数据行”而COUNT(*) FILTER (WHERE ...)是PostgreSQL的专用语法效果类似但语义更清晰。两个写法都支持我一般优先用FILTER因为它只关注过滤条件不需要像CASE那样特意构造0和1。如果你还要统计满足条件的金额总和、平均值CASE WHEN几乎是唯一顺手的选择。因为FILTER只负责计数SUM(CASE WHEN ... THEN amount END)可以直接聚合出对应金额。注意这里我没有写ELSE 0原因是金额金额字段本身不会是负数表达式里未命中的分支返回NULL而SUM会自动忽略NULL结果等价于0。但如果你需要区分“没有满足条件记录”和“满足条件但金额为0”这两种情况就不能这么做否则会混淆NULL和0。2.2 经典行转列把明细数据变成宽表报表行转列是CASE WHEN最出名的应用场景。假设有一张每个销售每天的业绩表你想把不同日期的业绩展示在同一行形成每个销售一条记录、各天各自一列的报表形式SELECT sales_name, MAX(CASE WHEN stat_date 2025-01-01 THEN daily_amount ELSE 0 END) AS day01, MAX(CASE WHEN stat_date 2025-01-02 THEN daily_amount ELSE 0 END) AS day02, MAX(CASE WHEN stat_date 2025-01-03 THEN daily_amount ELSE 0 END) AS day03 FROM sales_daily WHERE stat_date BETWEEN 2025-01-01 AND 2025-01-03 GROUP BY sales_name;为什么这里用MAX配合CASE因为还按sales_name分组后每个销售仍然可能对应多行数据你要做的是把符合条件的那个值取出来放到对应列MAX可以让同一分组内没有命中的行返回ELSE定义的值、命中的行返回真实值。行转列适合固定的列数场景。如果有动态生成列的需求即报表中的日期是变动的就要用SQL动态拼接的方式来实现把需要转列的日期遍历出来拼成一条完整的SQL再执行。这种做法的场景通常是在报表工具或程序里实现不适合放在数据库的存储过程里硬写。2.3 在UPDATE语句里用CASE WHEN批量修正数据CASE WHEN除了用在SELECT也经常配合UPDATE来批量修正数据。比如线上有个表的历史数据中某个字段的值因历史迁移错位了需要按不同条件做不同修正UPDATE product_info SET price CASE WHEN price IS NULL THEN default_price WHEN price 1 AND category sample THEN 1 ELSE price END, updated_at now() WHERE id IN (SELECT id FROM temp_correct_list);这里的关键逻辑是每行记录执行自己的条件判断按不同情况写入不同的值。这样一次UPDATE可以代替多条UPDATE语句减少事务次数和锁持有的时间。对于线上核心表来说这个改进在性能上意义很大。另外要注意UPDATE里CASE WHEN的ELSE分支如果你的某个条件分支不满足时希望保留原值ELSE必须写原字段名而不能不写ELSE就让其变成NULL。我在一家公司处理账单表时曾经因为漏掉ELSE分支把一批本不该更新的行的字段直接置为了NULL后续补救了两天才恢复完整数据。这个教训让我以后写批量UPDATE时都会习惯逐条检查每个分支的覆盖范围。3. ORDER BY排序与CASE嵌套让排序规则和业务逻辑对齐3.1 自定义排序规则把业务优先级写进ORDER BY很多业务里数据需要按业务层级排序比如工单优先处理级别、客户等级、事件紧急程度。如果只用字段本身的字典序或者创建时间排序无法体现业务优先级。CASE WHEN放在ORDER BY里可以完美解决这个问题SELECT order_no, pay_status, create_time FROM trade_order WHERE date_trunc(day, create_time) CURRENT_DATE ORDER BY CASE WHEN pay_status 1 THEN 1 WHEN pay_status 2 THEN 0 WHEN pay_status 3 THEN 2 ELSE 3 END, create_time DESC;这里的排序结果是已支付状态2优先展示未支付状态1其次支付失败状态3再次其他情况垫底。每个分组内部再按创建时间倒序。这个方法在待处理工单页面、客服后台列表、风控审核列表这类场景里很实用一行CASE WHEN就能把业务顺序和SQL排序对齐。同理你也可以在ORDER BY里用CASE WHEN把一个字段映射成数值后再排序比如按客户等级把“VIP”“普通”“潜在”映射成数字1、2、3。排序规则建议写在SQL层面而不是程序里因为数据库里的排序可以直接利用索引程序排序则要全部捞回来再排数据量一大差距就出来了。3.2 嵌套CASE的三种写法对比业务条件复杂时嵌套CASE不可避免。嵌套写法有三种典型形式。第一种是完全嵌套即THEN后面再跟一个CASESELECT CASE WHEN score 90 THEN CASE WHEN score 95 THEN A ELSE A END WHEN score 80 THEN B ELSE C END AS grade FROM student_score;第二种是扁平化把条件拆成多个细分分支SELECT CASE WHEN score 95 THEN A WHEN score 90 THEN A WHEN score 80 THEN B ELSE C END AS grade FROM student_score;第三种是把条件组合在同一个WHEN里SELECT CASE WHEN score 90 AND perfect_rate 0.95 THEN A WHEN score 90 THEN A WHEN score 80 THEN B ELSE C END AS grade FROM student_score;我一般优先选扁平化写法主要是因为逻辑更清晰、调试更直观。嵌套CASE一旦超过两层阅读成本会飙升而且容易出现WHERE分支覆盖顺序的bug。如果逻辑实在复杂也可以考虑先在子查询里用两次CASE分别计算不同的标量再在外部对结果做判断。这样每个CASE的职责单一排查时只需定位一层。3.3 一个踩坑示例嵌套时分支顺序颠倒的隐蔽问题有一次同事写了一段类似下面的嵌套CASE用于把“用户年龄区间会员等级”映射为“分层标签”SELECT CASE WHEN age 30 THEN CASE WHEN member_level 3 THEN 年轻高价值用户 ELSE 年轻普通用户 END WHEN age 30 AND age 60 THEN CASE WHEN member_level 3 THEN 中年高价值用户 ELSE 中年普通用户 END ELSE 老年用户 END AS user_label FROM user_profile;看起来清晰明了但实际跑出来的结果中“年轻高价值用户”的人数明显偏少。排查后发现原因不是CASE写错了而是age字段本身存在NULL值且NULL比较的结果是NULL所有WHEN条件都不成立这批用户全部落到了ELSE分支的“老年用户”里。这个问题的教训在于当表里有NULL字段时嵌套CASE的每个外层分支都应该先判断NULL或者在最前面加一个ELSE条件。类似“age 30 AND age 60”这类范围判断无法捕获NULL值。我在处理类似的用户分层逻辑时会先加上一个“WHEN age IS NULL THEN 未知”的兜底分支再进入年龄判断。逻辑多了几个字但结果可预期得多。4. 多字段组合与性能优化CASE WHEN不是简单堆条件4.1 多字段联合条件用布尔逻辑做条件编排业务需求经常是多个字段联合判定比如判断一笔订单是否属于“高优先级用户的高额订单”。多个条件都可以放在一个WHEN里用AND、OR、NOT连接SELECT order_no, CASE WHEN vip_level 3 AND order_amount 5000 AND is_abnormal 0 THEN 重点优质单 WHEN vip_level 3 OR order_amount 5000 THEN 潜力优质单 ELSE 普通单 END AS quality_label FROM trade_order WHERE is_deleted 0;这个场景的要点是优先级顺序最严格的条件在前最宽松的条件在后。如果把“潜力优质单”的分支放在前面那么“重点优质单”永远匹配不到因为满足VIP3或金额5000的记录在第一个分支就已经全部被捕获了。多字段联合判断时还要注意PostgreSQL对于三值逻辑的处理。WHERE中只保留了满足条件结果为TRUE的行但WHEN的分支判断也是同样的逻辑只有结果为TRUE才会进入分支。所以如果一个WHEN表达式中AND的两边任何一个为NULL整个表达式就不是TRUE条件不成立分支被跳过。4.2 条件判断里的NULL处理三板斧针对NULL我总结了三板斧的处理方式。第一板斧每次写CASE分支前心里快速过一遍可能为NULL的字段。按字段上一轮的取值情况判断是否可能NULL这是经验但大多数宕数据都是从某个字段NULL开始的。第二板斧使用COALESCE或NULLIF配合CASE做兜底。比如SELECT CASE WHEN COALESCE(score, 0) 60 THEN 及格 ELSE 不及格 END AS pass_label FROM exam_record;这里把NULL先转成0再比较避免了NULL判断的陷阱。第三板斧直接在WHERE里排除NULL行的场景把NULL行单独过滤出来。这适合你本身不需要参与判断活动数据的情况。SELECT CASE WHEN score 60 THEN 及格 ELSE 不及格 END AS pass_label, score FROM exam_record WHERE score IS NOT NULL;注意COALESCE和NULLIF都不会改变原字段值如果在CASE表达式中使用它们原字段仍然保留原始值。两者都是纯运算函数不影响后续的统计结果。4.3 表达式索引让CASE WHEN在WHERE里提速在WHERE子句里使用CASE WHEN时很容易遇到查询变慢的情况。原因很简单普通B-tree索引基于原字段的值构建而CASE WHEN通常会改造字段值索引无法被直接用于条件过滤。解决问题的方法是创建表达式索引。比如查询中经常出现“根据打折后的价格筛选低价商品”这类逻辑SELECT product_id, CASE WHEN is_discount 1 THEN price * 0.8 ELSE price END AS real_price FROM product_sku WHERE CASE WHEN is_discount 1 THEN price * 0.8 ELSE price END 500;这个查询每次都会对每一行计算一次表达式再过滤结果。数据量到50万以后响应就会变得很慢。解决办法是建一个表达式索引CREATE INDEX idx_product_sku_real_price ON product_sku ( (CASE WHEN is_discount 1 THEN price * 0.8 ELSE price END) );这样查询可以走索引快速找到符合条件的行。这个优化特别适合固定查询模式的场景。如果CASE表达式参数不固定比如打折比例每次传入的值都不一样表达式索引就无法命中因为索引是对固定表达式构建的。另外注意创建表达式索引时表达式要和你查询中的表达式保持一致包括CASE的缩进、括号、ELSE值都不能差。PostgreSQL会按字面匹配表达式来判断是否命中索引。4.4 当CASE WHEN遇上GROUP BY时的性能取舍CASE WHEN与GROUP BY结合时GROUP BY本身会先聚合聚合后CASE的结果一般是最终结果。但如果CASE里含有聚合函数逻辑就会复杂许多也容易出现慢查询。比如你想统计每一类标签的数量可以这样写SELECT CASE WHEN order_amount 10000 THEN 大额单 WHEN order_amount 5000 THEN 中等单 ELSE 普通单 END AS amount_level, COUNT(*) AS cnt FROM trade_order WHERE create_time now() - interval 3 months GROUP BY CASE WHEN order_amount 10000 THEN 大额单 WHEN order_amount 5000 THEN 中等单 ELSE 普通单 END;注意GROUP BY后面的表达式和SELECT里的CASE是同一个表达式而不是一个别名。PostgreSQL在GROUP BY阶段会按表达式本身来分组别名不一定被允许引用。在PostgreSQL中GROUP BY可以引用SELECT列表中的别名但某些情况下语法比较严格。所以最安全的方式是直接写表达式。如果这个查询的命中行数大而且你经常要这个统计结果可以考虑建一个基于相同表达式的索引来加速GROUP BY的分组过程。表达式索引除了能被WHERE使用也能在GROUP BY和ORDER BY中被用来加速排序和分组。5. 工程化实践与常见问题CASE WHEN从“会写”到“写好”5.1 常见错误速查表我把自己几年间在项目中遇到过的CASE相关报错和坑整理成了一个速查表方便直接对照排查。错误/问题可能原因解决方案返回类型不一致有的分支返回字符串、有的返回数字统一各分支返回值类型必要时用::text或::int做显式转换NULL结果意外出现条件未全覆盖且没有ELSE补ELSE分支或预先处理NULL值查询结果为整数而非精度值分支中整数除以整数整数运算导致精度丢失将其中一个操作数转为numeric如1.0或::numeric嵌套条件命中顺序异常外层宽泛条件写在了严格条件前面由严格到宽松排列分支顺序SQL执行计划走全表扫描WHERE里使用了表达式且没有表达式索引建与查询一致的表达式索引结果和预期不一致GROUP BY中用了别名而不是完整表达式在GROUP BY中直接写完整CASE表达式性能骤降CASE表达式里嵌套了子查询优先考虑子查询或JOIN方式替代遇到过最多的问题是返回类型不一致。比如CASE分支里THEN 0和THEN 无数据混用这种写法在PostgreSQL里通常会执行时直接报错。建议建表时或者写SQL前就想好每个分支的数据类型。如果确实需要把数字显示为字符串可以用CAST显式转换让数据库引擎帮你在返回值上做统一的类型转换。5.2 CASE WHEN与子查询的取舍什么时候不该用CASECASE WHEN虽然强大但不适合承担过于复杂的业务计算。比如一个分支里要查另一个表的数据你可以写子查询但性能往往会差。还有一种情况是分支逻辑里需要多次扫描同一张表这时候用CASE嵌套子查询会让执行计划变得非常昂贵且难追踪。我处理过一个业务需要根据用户的最近一次登录时间、最近一次消费时间、累计消费金额三个维度判断用户价值等级。最初别人写的SQL里把三个维度的查询都塞进了CASE的THEN里导致一次查询子查询扫描了三次用户流水表执行时间高达十几秒。优化方法是先在一个子查询里把最近登录时间、最近消费时间、累计消费金额计算出来再在外层用CASE做纯条件判断WITH user_metric AS ( SELECT user_id, MAX(login_time) AS last_login_time, MAX(pay_time) AS last_pay_time, SUM(amount) AS total_amount FROM user_behavior_log GROUP BY user_id ) SELECT user_id, CASE WHEN last_login_time now() - interval 7 days AND last_pay_time now() - interval 30 days AND total_amount 10000 THEN 高价值活跃用户 WHEN last_login_time now() - interval 30 days THEN 活跃用户 WHEN total_amount 5000 THEN 高消费低活跃用户 ELSE 普通用户 END AS user_tag FROM user_metric;CASE的定位是“分支判断”而不是“延迟计算”。一旦分支里需要做聚合运算、多表关联、开窗函数就应该先通过子查询或CTE把数据准备好再让CASE用已经准备好的字段做纯条件映射。这个设计习惯能让SQL保持清晰也会让执行计划更稳定。5.3 与LATERAL、窗口函数配合的进阶玩法CASE WHEN和窗口函数搭配也很常见。比如按金额排序时你想优先展示“重点用户”的单子可以在ROW_NUMBER()的ORDER BY中嵌入CASESELECT order_no, user_id, order_amount, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY CASE WHEN is_star_user 1 THEN 0 ELSE 1 END, order_amount DESC ) AS user_order_rank FROM trade_order WHERE date_trunc(month, create_time) CURRENT_DATE;这个写法的效果是每个用户内部星标用户的订单排前面非星标用户的订单排后面组内再按金额倒序。实际应用场景常见于电商运营页面把核心用户的重点订单优先展示。CASE与LATERAL配合也很实用。LATERAL可以让你在子查询里引用外层表的字段然后在子查询里做若干条件判断再返回多个列。比如SELECT o.order_no, t.level_name, t.handle_days FROM trade_order o CROSS JOIN LATERAL ( SELECT CASE WHEN o.amount 10000 AND o.freight 0 THEN 高级订单 WHEN o.amount 5000 THEN 中级订单 ELSE 普通订单 END AS level_name, CASE WHEN o.amount 10000 THEN 1 WHEN o.amount 5000 THEN 2 ELSE 3 END AS handle_days ) t;这种写法适合CASE计算结果被多个外层列引用的情况。LATERAL子查询把判断逻辑单独封装外层代码不重复写CASE避免了一处改动需要同步多处的问题。我一般会把较复杂的CASE逻辑封装在LATERAL或CTE里方便后续调试。5.4 ORM与SQL中CASE WHEN的取舍建议很多用ORM框架写业务逻辑的同事喜欢把分支判断写到Java或Python代码里但遇到批量数据转换时代码里逐行判断会有两个明显问题第一查询会把可能需要的所有数据全量拉回应用层网络和内存开销大第二应用层逐行遍历再操作性能比SQL里直接完成差两三个数量级。什么时候适合在SQL里用CASE WHEN什么时候不适合我的判断标准是数据处理的位置。如果属于“数据报表、统计查询、批量加工”直接在SQL里用CASE WHEN如果属于“复杂的业务规则流程、需要多渠道获取更多信息才能决策”留在应用层处理更合理毕竟SQL层的判断条件和应用层的事务逻辑混在一起会很难维护。举个例子一个微信公众号消息推送业务要根据用户的积分、注册天数、订单偏好选择不同的推送文案这种规则未来变化频繁而且依赖很多应用层数据不适合在SQL里写CASE。但如果只是需要统计每个用户应该推送的文案类型数量那就适合在SQL里做汇总先用CASE加工出推送类型再用GROUP BY统计。从维护角度讲SQL里的CASE分支一旦超过5个最好加注释说明每个分支的业务含义否则三个月后自己看都会发懵。我习惯在每个CASE的WHEN行上加一行注释标明对应业务条件能省很多维护成本。结尾一点个人经验我用了好几年PostgreSQLCASE WHEN是日常写SQL时最顺手又最容易出细节问题的功能。它看起来像if-else的SQL翻译但只有用熟了才会理解它的设计逻辑分支按顺序匹配、不带ELSE返回NULL、聚合上下文里配合条件计数和条件求和、排序里做业务优先级映射。这些都是普通文档里不会强调、但实际写报表时最能提效的细节。我最开始频繁踩坑的场景是忘记ELSE导致结果集里出现NULL以及在ORDER BY里写CASE时因为条件顺序处理不对导致排序结果不直观。后来养成了一个习惯每次写完CASE先数一下所有WHEN条件是否能覆盖业务里的全部取值如果覆盖不了就写ELSE再复查一下每个分支返回值的类型是否完全一致。这个小习惯大大减少了线上报错和返工率。如果你想进阶建议把CASE WHEN与表达式索引、窗口函数、CTE组合起来用。比如在CTE里先用CASE把用户打标外层再基于标签做聚合统计这种写法不仅可读性好性能也远比不停嵌套子查询可靠。项目里遇到报表逻辑时我也总会先想能不能用CASE WHEN解决而不是直接拿到应用层循环。这个习惯让我的SQL简洁了非常多。