相信不少写SQL的朋友都遇到过这样的场景订单表里存的是status1/2/3老板却要你在报表里显示“已支付/已发货/已取消”统计的时候明明只想看一行结果却要把不同状态的数据拆成好几列排序的时候按字母排完全不符合业务预期非得按“处理中→已完成→已取消”这种自定义顺序来。这些需求靠WHERE和GROUP BY硬写会很别扭而MySQL里专门解决这类问题的一个表达式就是case。case表达式是SQL里少有的、能让你在查询中直接写“逻辑判断”的语法。它就相当于程序里的if...else但比if更灵活既能用在SELECT里做条件计算也能用在ORDER BY里做自定义排序还能配合聚合函数实现“行转列”这类看起来很高端的操作。这篇文章我准备把case的两种写法、五个高频实战场景、一个完整的综合案例以及我踩过的几个坑一次讲清楚。不管是刚接触SQL的初学者还是写了好几年SQL想系统梳理一下的老手都能从这里拿到可以直接抄的写法。1. case表达式的两种写法简单case与搜索casecase表达式在MySQL里有两种语法形式。很多人只熟悉其中一种或者两种混着用却没搞懂区别。我先从语法层面拆开讲因为后面所有场景都建立在这两种写法之上。理解了它们你的SQL会变得非常灵活。1.1 简单casesimple case写法详解简单case的语法结构是CASE 表达式 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ... ELSE 默认结果 END它的执行逻辑很像程序里的switch语句MySQL会把CASE后面的那个表达式计算一次然后从上到下逐个和WHEN后面的值做等值比较。一旦匹配上就返回对应的THEN结果后面不再继续判断。如果所有WHEN都不匹配就返回ELSE的结果如果连ELSE都没写那就返回NULL。举个例子一张订单表里有一个status字段1表示待支付2表示已支付3表示已取消。想把它转成汉字可以这样写SELECT order_id, CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已取消 ELSE 未知状态 END AS status_text FROM order_base;这里CASE status后面的status字段就是那个“只计算一次”的表达式。需要注意的关键点是简单case只能做等值比较也就是status 1、status 2这种。如果你想判断amount 1000或者status IN (1,2)这种范围或集合条件简单case就无能为力了这时候必须用搜索case。1.2 搜索casesearched case写法详解搜索case的语法结构是CASE WHEN 条件表达式1 THEN 结果1 WHEN 条件表达式2 THEN 结果2 ... ELSE 默认结果 END注意区别CASE后面不跟字段WHEN后面直接跟一个完整的条件表达式。它的执行逻辑类似else if链从上到下依次判断每个WHEN后的条件是否为真第一个为真的分支胜出返回它对应的THEN结果。如果所有条件都不为真返回ELSE没有ELSE就返回NULL。同样是上面那个状态转换的需求用搜索case写是这样的SELECT order_id, CASE WHEN status 1 THEN 待支付 WHEN status 2 THEN 已支付 WHEN status 3 THEN 已取消 ELSE 未知状态 END AS status_text FROM order_base;光看这个例子两种写法好像只是格式不同。但搜索case真正厉害的地方在于WHEN后面可以是任意条件表达式包括比较、范围、模糊匹配、子查询等等。比如判断一个订单金额属于哪个档位SELECT order_id, amount, CASE WHEN amount 100 THEN 小额订单 WHEN amount 500 THEN 中额订单 WHEN amount 1000 THEN 大额订单 ELSE 超大额订单 END AS order_level FROM order_base;这就是搜索case的应用空间它把SQL从“只能做等值判断”扩展到了“可以写任意逻辑条件”的层面。1.3 简单case与搜索case的区别和选型为了让你一眼看清两种写法的差异我直接整理了一个对比表对比项简单case搜索case语法位置CASE 字段后接WHEN 值CASE后直接接WHEN 条件判断方式等值比较任意条件表达式灵活性低只能判断“等于”高能写范围、模糊、多条件组合典型场景码值翻译、状态转换区间判断、复杂业务规则、行转列NULL处理CASE NULL ...不会匹配IS NULL可以直接写WHEN 字段 IS NULL我的选型建议很简单除非你只是做个简单的码值翻译否则一律用搜索case。原因有两个第一搜索case覆盖面更广简单case能做的事它都能做反过来则不成立。第二搜索case的可读性更好WHEN status 1比CASE status WHEN 1更直白别人看代码的时候不用去猜那个字段是什么含义。我自己在实际项目里写的case九成以上都是搜索case。简单case这里有个常见的认知误区很多人以为CASE NULL WHEN NULL THEN ...能匹配到空值实际上NULL NULL的结果是NULL不会走任何WHEN分支必须写成CASE WHEN 字段 IS NULL THEN ...。2. 高频实战场景从排序到行转列语法只是工具真正体现case价值的是它在各种实战场景中的应用。我在这里挑五个出现频率最高的场景每一个都给出完整可跑的SQL并说明背后的思路。这些写法如果你都吃透了日常报表开发里的绝大多数需求都能用一套case解决。2.1 自定义排序用case给排序规则“造序号”一个常见需求一个任务表里有状态字段包含“待处理”、“处理中”、“已完成”、“已取消”。你想让查询结果按照“处理中 → 已完成 → 待处理 → 已取消”的顺序排列。直接用ORDER BY status肯定做不到因为字符串排序是按字母顺序来的那会是“处理中、已完成、待处理、已取消”这种奇怪的顺序。这时候就可以在SELECT里用一个case把状态映射成数字序号再在ORDER BY里用这个序号排序SELECT task_id, task_name, status FROM task_info ORDER BY CASE status WHEN 处理中 THEN 1 WHEN 已完成 THEN 2 WHEN 待处理 THEN 3 WHEN 已取消 THEN 4 ELSE 5 END;这里的关键点是ORDER BY后面可以直接放一个CASE表达式MySQL会先计算每一行的序号再按序号排序。如果状态值不在预期列表中ELSE 5保证了它们排在最后不会因为NULL跑到最前面。这个写法在任务列表、审批流、工单系统里非常常用而且比用FIELD()函数更通用、更容易被其他数据库兼容。2.2 CASE配合聚合函数实现行转列假设你和老板汇报数据老板想要的是这样的表一行一个月份每一行里分别有“下单数”、“完成数”、“取消数”三列。可是订单表里只有status字段区分状态怎么把一列拆成三列核心写法是用CASE在聚合函数内部做条件过滤。比如SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS total_cnt, COUNT(CASE WHEN status 已完成 THEN 1 END) AS finished_cnt, COUNT(CASE WHEN status 已取消 THEN 1 END) AS canceled_cnt FROM order_base GROUP BY DATE_FORMAT(create_time, %Y-%m);这段SQL的逻辑是这样的COUNT(CASE WHEN status 已完成 THEN 1 END)中case相当于一个过滤阀门——当行的状态是“已完成”时THEN 1让聚合函数对这一行计数当状态不是“已完成”时THEN后面的值没有被定义CASE返回NULL。COUNT函数在计算时只会统计非NULL值因此COUNT里面自然就只统计了“已完成”的行数。这个场景就是典型的“行转列”本质是利用CASE返回NULL或非NULL来控制聚合函数的统计范围。这里也可以写成SUM(CASE WHEN status已完成 THEN 1 ELSE 0 END)效果几乎一样只是用COUNT更简洁。COUNT(CASE...)和SUM(CASE...)的区别在于前者依赖“NULL不计数的特性”后者依赖“0不参与累加的特性”两者殊途同归按自己的习惯选择就好。2.3 条件聚合在同一个分组里计算多口径指标行转列只是条件聚合的一种表现。条件聚合更常见的使用场景是不拆列但是要在同一个分组内算多个指标比如“总订单数里大额订单占多少比例”。SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS total_cnt, COUNT(CASE WHEN amount 500 THEN 1 END) AS big_order_cnt, ROUND( COUNT(CASE WHEN amount 500 THEN 1 END) / COUNT(*) * 100, 2 ) AS big_order_rate FROM order_base GROUP BY DATE_FORMAT(create_time, %Y-%m);这个用法在数据分析中极其常见总用户数、活跃用户数、付费用户数全部可以在同一个GROUP BY里算出来不用写多个子查询再JOIN。这里有一个细节要注意COUNT统计的是行数不是金额总和。如果你要按金额条件聚合比如“金额大于500的订单总金额”那就要用在SUM里配合CASESUM(CASE WHEN amount 500 THEN amount ELSE 0 END) AS big_order_amount2.4 分段逻辑对连续数值做区间归类有一类业务需求是给连续变量分类比如用户的年度消费金额分为“普通用户”、“中级用户”、“高级用户”、“VIP用户”。用搜索case写区间判断特别直观SELECT user_id, total_amount, CASE WHEN total_amount 1000 THEN 普通用户 WHEN total_amount 5000 THEN 中级用户 WHEN total_amount 20000 THEN 高级用户 ELSE VIP用户 END AS user_level FROM user_stat;这里有个容易被忽略的细节分支顺序很重要。搜索case是从上往下匹配的第一个满足条件的分支就会命中。所以区间判断里要么全部用区间下限判断并从小到大排列如 1000、 5000、 20000要么从大到小排列配合下限判断。千万不要写成交叉重叠的区间否则后面的分支永远不会被命中而且很难排查。比如WHEN total_amount 1000 THEN A和WHEN total_amount 5000 THEN B同时出现那金额6000的会永远走第一个分支。这个写法的好处是它可以在不修改表结构的前提下在查询结果里动态生成一个分类字段直接用于后续的报表分组。比如你就想按用户等级统计人数可以在子查询里先生成user_level外层再GROUP BY user_level非常方便。2.5 数据清洗与维度归并把散乱的值规整成标准类别实际业务数据往往又散又乱特别是渠道来源、支付方式这类维度字段会有很多相近但又不完全一样的取值。比如线上渠道里有的是“小程序”有的是“App”还有“H5”如果直接按字段分组报表会非常碎。用case归并维度字段是数据清洗阶段最常用的手段SELECT CASE WHEN channel IN (小程序, App, H5) THEN 线上 WHEN channel IN (门店, 专柜) THEN 线下 ELSE 其他渠道 END AS channel_group, COUNT(*) AS order_cnt FROM order_base GROUP BY CASE WHEN channel IN (小程序, App, H5) THEN 线上 WHEN channel IN (门店, 专柜) THEN 线下 ELSE 其他渠道 END;注意到一个细节这里GROUP BY后面没有用别名而是重复写了一遍完整的case表达式。这是因为MySQL虽然在某些时候允许GROUP BY接SELECT别名但为了严谨和兼容性在GROUP BY里重复case是更稳妥的做法。当然也可以更简洁地写成SELECT CASE WHEN channel IN (小程序, App, H5) THEN 线上 WHEN channel IN (门店, 专柜) THEN 线下 ELSE 其他渠道 END AS channel_group, COUNT(*) AS order_cnt FROM order_base GROUP BY channel_group;这种写法在MySQL里是允许的但在严格模式下部分版本可能不友好。如果你不确定线上环境的MySQL版本建议用第一种完整表达式的写法。归并维度的思路本质上是“把多样性收敛为标准口径”在数据仓库和BI报表建设中会频繁用到。3. 综合实战案例订单状态统计与渠道行转列为了让你串起前面所有的知识点我准备用一个完整的案例走一遍。这个案例虚构自一个电商业务的后台报表需求表结构和数据都是演示用途但逻辑真实可落地。假设有一张订单表order_base结构如下CREATE TABLE order_base ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, channel VARCHAR(16) COMMENT 渠道小程序/App/H5/门店, amount DECIMAL(10,2) NOT NULL, status VARCHAR(16) COMMENT 状态待支付/已完成/已取消, create_time DATETIME NOT NULL );业务方提了一个需求今天需要出一份日报按渠道统计订单情况要求一行一个渠道包含总订单数、已完成订单数、已完成订单金额、已完成率并按渠道横向对比。直接上最终SQLSELECT CASE WHEN channel IN (小程序, App, H5) THEN 线上 WHEN channel 门店 THEN 线下 ELSE 未知 END AS channel_group, COUNT(*) AS total_cnt, COUNT(CASE WHEN status 已完成 THEN 1 END) AS finished_cnt, SUM(CASE WHEN status 已完成 THEN amount ELSE 0 END) AS finished_amount, ROUND( COUNT(CASE WHEN status 已完成 THEN 1 END) / COUNT(*) * 100, 2 ) AS finished_rate FROM order_base GROUP BY CASE WHEN channel IN (小程序, App, H5) THEN 线上 WHEN channel 门店 THEN 线下 ELSE 未知 END;我们来逐段拆解这段SQL第一部分是CASE归并渠道维度把三个线上渠道合并为“线上”门店归为“线下”其他值归为“未知”。这一步解决了渠道维度过碎的问题。第二部分是COUNT(CASE WHEN status 已完成 THEN 1 END)这就是前面讲过的条件计数只统计已完成订单数。第三部分是SUM(CASE WHEN status 已完成 THEN amount ELSE 0 END)计算已完成订单的金额总和。第四部分是完成率计算。这里有个新手很容易踩的坑如果直接写COUNT(CASE...) / COUNT(*)得到的结果是整数不是百分比。MySQL中两个整数相除结果是浮点数所以理论上可以直接做小数的除法但为了保证精度的可控性我还是建议习惯性地在外面套ROUND(..., 2)并且注意分母不能为0。如果你的业务表在某个渠道下一个订单都没有那COUNT(*)就是0直接除会报除零警告并返回NULL。这种情况下可以用IFNULL包裹一下IFNULL(ROUND(COUNT(CASE WHEN status 已完成 THEN 1 END) / NULLIF(COUNT(*), 0) * 100, 2), 0)NULLIF(COUNT(*), 0)的作用是如果COUNT(*)为0把它转换为NULL这样除法结果就是NULL外层IFNULL再把它显示为0。这一段是整个案例里最需要仔细品的部分因为除零问题在报表开发中太常见了。执行出来的结果大概长这样channel_grouptotal_cntfinished_cntfinished_amountfinished_rate线上32629845210.0091.41线下74558875.5074.32这个案例虽然字段是虚构的但查询结构是完全可以直接搬到真实业务里的。如果业务方又要“按天按渠道”看趋势你只需要把GROUP BY改成DATE_FORMAT(create_time, %Y-%m-%d)再加上渠道分组即可核心的CASE逻辑不用动。4. 最容易踩的坑case用法排查实录case写起来简单但真正在复杂SQL里用起来坑也不少。这一节我把自己这些年见过的、踩过的坑整理成了一份排查速查表并挑重点展开讲。每一条都是真实出现过的教训。常见问题现象原因解决方案漏写ELSE导致数据少算聚合结果比预期少未匹配的行返回NULL聚合函数不计NULL写每条CASE时显式加ELSECASE后表达式与NULL比较NULL行不进入任何分支NULL NULL不成立用WHEN 字段 IS NULLTHEN分支类型不一致结果出现奇怪数字或字符串MySQL隐式转换统一THEN分支返回的类型在WHERE中使用CASE包裹索引列查询变慢索引失效CASE表达式破坏了原始列改写为OR/AND组合直接比较分支顺序错误部分区间永远不命中搜索case从上往下匹配按区间顺序严格排列CASE里嵌套子查询/函数过重查询性能明显下降每行都执行子查询先JOIN或提取公共表表达式第一个坑漏写ELSE这是最常见的。我见过一个统计需求CASE WHEN status 已完成 THEN amount END结果因为表中存在少量“已取消”状态所有已取消订单的金额都被记成了NULLSUM计算后金额凭空少了几万块。排查了很长时间才定位到是漏写ELSE 0导致的。所以我的习惯是每条CASE都写ELSE哪怕确定不会走到也显式写ELSE NULL这样至少后续维护的人知道你是有意处理的。第二个坑CASE后面的表达式与NULL比较。前面提过CASE NULL WHEN NULL THEN 1 END不会匹配这在做空值分类时很容易出错。比如你要给用户打标签没填写手机号的用户应该归类为“无手机号”如果你写CASE mobile WHEN NULL THEN 无手机号 ...是无效的因为NULL NULL的判断结果不是真。必须写CASE WHEN mobile IS NULL THEN 无手机号 ...。第三个坑THEN分支返回类型不一致。比如一个CASE里有的分支返回字符串有的分支返回数字MySQL会做隐式转换结果可能不是你要的。最典型的是CONCAT(CASE WHEN a 1 THEN 金额: ELSE amount END)当ELSE分支是数字时MySQL会试图把字符串和数字做合并如果数字那边不是纯数字字符串可能报错或者结果里出现“0”、“NULL”这种奇怪拼接。保持每个分支的类型一致是一个好习惯。第四个坑在WHERE条件里用CASE包裹索引列。比如你想表达“如果订单金额大于1000就看状态否则看渠道”有些人会写WHERE CASE WHEN amount 1000 THEN status ELSE channel END 已完成。这个写法语法上没问题但数据库无法对这个表达式使用索引只能全表扫描。正确的做法是改写成逻辑等价但可以走索引的OR组合WHERE (amount 1000 AND status 已完成) OR (amount 1000 AND channel 已完成);这里的规律是在WHERE里直接对裸字段做比较索引才能生效任何把字段包在函数或表达式里的行为都会让索引退化。第五个坑分支顺序混乱。这个我在分段逻辑部分讲过最典型的错误是WHEN amount 1000 THEN 大和WHEN amount 500 THEN 中同时出现金额800的订单明明应该分类为“中”却走进了“大”分支。解决办法是区间条件严格按大小顺序排列并养成从小到大或从大到小书写、逐段覆盖的习惯。第六个坑性能问题。CASE里如果嵌了子查询MySQL会针对每一行去执行一次子查询数据量一大性能直线下降。比如CASE WHEN (SELECT ...) 100 THEN ...这种写法除非确认子查询结果非常小且有索引支撑否则建议先把子查询结果JOIN进来或者用公共表表达式提前算好再在CASE里直接引用结果字段。5. 我的一些使用习惯与调试心得最后分享几个我在实际项目里形成的习惯不一定是最优解但都是在踩过坑之后沉淀下来的实用经验。第一个习惯是每条CASE都写ELSE。不管业务方说“这些值都已经覆盖了”我还是会写一个ELSE兜底。因为数据是动态的今天没有的状态明天可能就出现了。有了ELSE至少结果里能看到“未知”或“其他”而不是静默的NULL这能省下大量排查数据异常的时间。第二个习惯是开发阶段先用SELECT验证CASE分支再写进UPDATE或INSERT。有一个我印象很深的教训某次我给线上用户表打等级标签直接写了一条UPDATE ... SET level CASE WHEN ... END结果有个分支条件写反了导致一批高净值用户的等级被错误地改成了“普通用户”。恢复数据的过程非常痛苦。后来我养成了一个习惯凡是CASE要用于UPDATE或INSERT都会先在SELECT里把CASE的结果字段查出来对着数据量做一遍抽检确认每个分支都命中了预期数据再动手更新。这个习惯看起来多了一步操作却能在关键时刻避免灾难。第三个习惯是分支超过四五个的时候考虑用映射表代替CASE。比如状态码有20种每种对应一个中文名称那直接在SQL里写20个WHEN分支维护起来非常痛苦新加一个状态就得改SQL。这种情况更适合建一张状态码映射表用JOIN关联新增状态只需要在表里加一行。判断“用CASE还是用映射表”的经验法则是分支少、规则个性化强、只在一个查询里用它就用CASE分支多、规则统一、多个报表都要用就用映射表。第四个习惯是优先使用搜索case而不是简单case。就算只是简单的码值翻译搜索case的WHEN status 1也比CASE status WHEN 1更清晰而且在以后需要增加范围条件时可以直接扩展不需要改语法结构。最后一个实用技巧CASE可以和GROUP_CONCAT配合实现比行转列更灵活的输出。比如你想把每个用户的已支付订单号和已取消订单号分别拼成字符串输出到两列里SELECT user_id, GROUP_CONCAT(CASE WHEN status 已支付 THEN order_no END) AS paid_orders, GROUP_CONCAT(CASE WHEN status 已取消 THEN order_no END) AS canceled_orders FROM order_base GROUP BY user_id;GROUP_CONCAT会跳过NULL值所以CASE返回NULL的订单号不会拼进字符串里。这个组合在做用户维度的一行摘要时非常好用。MySQL的case表达式是一个越用越顺的利器它在SQL里提供了一种“在数据行内部做逻辑分支”的能力。你不需要为了一个简单的条件判断去写一堆子查询也不用在业务代码里循环处理数据。先把两种语法吃透再把那几个实战场景练熟日常SQL开发里遇到“依赖某个字段值做不同处理”的需求时你自然就会想到它。
相信不少写SQL的朋友都遇到过这样的场景订单表里存的是status1/2/3老板却要你在报表里显示“已支付/已发货/已取消”统计的时候明明只想看一行结果却要把不同状态的数据拆成好几列排序的时候按字母排完全不符合业务预期非得按“处理中→已完成→已取消”这种自定义顺序来。这些需求靠WHERE和GROUP BY硬写会很别扭而MySQL里专门解决这类问题的一个表达式就是case。case表达式是SQL里少有的、能让你在查询中直接写“逻辑判断”的语法。它就相当于程序里的if...else但比if更灵活既能用在SELECT里做条件计算也能用在ORDER BY里做自定义排序还能配合聚合函数实现“行转列”这类看起来很高端的操作。这篇文章我准备把case的两种写法、五个高频实战场景、一个完整的综合案例以及我踩过的几个坑一次讲清楚。不管是刚接触SQL的初学者还是写了好几年SQL想系统梳理一下的老手都能从这里拿到可以直接抄的写法。1. case表达式的两种写法简单case与搜索casecase表达式在MySQL里有两种语法形式。很多人只熟悉其中一种或者两种混着用却没搞懂区别。我先从语法层面拆开讲因为后面所有场景都建立在这两种写法之上。理解了它们你的SQL会变得非常灵活。1.1 简单casesimple case写法详解简单case的语法结构是CASE 表达式 WHEN 值1 THEN 结果1 WHEN 值2 THEN 结果2 ... ELSE 默认结果 END它的执行逻辑很像程序里的switch语句MySQL会把CASE后面的那个表达式计算一次然后从上到下逐个和WHEN后面的值做等值比较。一旦匹配上就返回对应的THEN结果后面不再继续判断。如果所有WHEN都不匹配就返回ELSE的结果如果连ELSE都没写那就返回NULL。举个例子一张订单表里有一个status字段1表示待支付2表示已支付3表示已取消。想把它转成汉字可以这样写SELECT order_id, CASE status WHEN 1 THEN 待支付 WHEN 2 THEN 已支付 WHEN 3 THEN 已取消 ELSE 未知状态 END AS status_text FROM order_base;这里CASE status后面的status字段就是那个“只计算一次”的表达式。需要注意的关键点是简单case只能做等值比较也就是status 1、status 2这种。如果你想判断amount 1000或者status IN (1,2)这种范围或集合条件简单case就无能为力了这时候必须用搜索case。1.2 搜索casesearched case写法详解搜索case的语法结构是CASE WHEN 条件表达式1 THEN 结果1 WHEN 条件表达式2 THEN 结果2 ... ELSE 默认结果 END注意区别CASE后面不跟字段WHEN后面直接跟一个完整的条件表达式。它的执行逻辑类似else if链从上到下依次判断每个WHEN后的条件是否为真第一个为真的分支胜出返回它对应的THEN结果。如果所有条件都不为真返回ELSE没有ELSE就返回NULL。同样是上面那个状态转换的需求用搜索case写是这样的SELECT order_id, CASE WHEN status 1 THEN 待支付 WHEN status 2 THEN 已支付 WHEN status 3 THEN 已取消 ELSE 未知状态 END AS status_text FROM order_base;光看这个例子两种写法好像只是格式不同。但搜索case真正厉害的地方在于WHEN后面可以是任意条件表达式包括比较、范围、模糊匹配、子查询等等。比如判断一个订单金额属于哪个档位SELECT order_id, amount, CASE WHEN amount 100 THEN 小额订单 WHEN amount 500 THEN 中额订单 WHEN amount 1000 THEN 大额订单 ELSE 超大额订单 END AS order_level FROM order_base;这就是搜索case的应用空间它把SQL从“只能做等值判断”扩展到了“可以写任意逻辑条件”的层面。1.3 简单case与搜索case的区别和选型为了让你一眼看清两种写法的差异我直接整理了一个对比表对比项简单case搜索case语法位置CASE 字段后接WHEN 值CASE后直接接WHEN 条件判断方式等值比较任意条件表达式灵活性低只能判断“等于”高能写范围、模糊、多条件组合典型场景码值翻译、状态转换区间判断、复杂业务规则、行转列NULL处理CASE NULL ...不会匹配IS NULL可以直接写WHEN 字段 IS NULL我的选型建议很简单除非你只是做个简单的码值翻译否则一律用搜索case。原因有两个第一搜索case覆盖面更广简单case能做的事它都能做反过来则不成立。第二搜索case的可读性更好WHEN status 1比CASE status WHEN 1更直白别人看代码的时候不用去猜那个字段是什么含义。我自己在实际项目里写的case九成以上都是搜索case。简单case这里有个常见的认知误区很多人以为CASE NULL WHEN NULL THEN ...能匹配到空值实际上NULL NULL的结果是NULL不会走任何WHEN分支必须写成CASE WHEN 字段 IS NULL THEN ...。2. 高频实战场景从排序到行转列语法只是工具真正体现case价值的是它在各种实战场景中的应用。我在这里挑五个出现频率最高的场景每一个都给出完整可跑的SQL并说明背后的思路。这些写法如果你都吃透了日常报表开发里的绝大多数需求都能用一套case解决。2.1 自定义排序用case给排序规则“造序号”一个常见需求一个任务表里有状态字段包含“待处理”、“处理中”、“已完成”、“已取消”。你想让查询结果按照“处理中 → 已完成 → 待处理 → 已取消”的顺序排列。直接用ORDER BY status肯定做不到因为字符串排序是按字母顺序来的那会是“处理中、已完成、待处理、已取消”这种奇怪的顺序。这时候就可以在SELECT里用一个case把状态映射成数字序号再在ORDER BY里用这个序号排序SELECT task_id, task_name, status FROM task_info ORDER BY CASE status WHEN 处理中 THEN 1 WHEN 已完成 THEN 2 WHEN 待处理 THEN 3 WHEN 已取消 THEN 4 ELSE 5 END;这里的关键点是ORDER BY后面可以直接放一个CASE表达式MySQL会先计算每一行的序号再按序号排序。如果状态值不在预期列表中ELSE 5保证了它们排在最后不会因为NULL跑到最前面。这个写法在任务列表、审批流、工单系统里非常常用而且比用FIELD()函数更通用、更容易被其他数据库兼容。2.2 CASE配合聚合函数实现行转列假设你和老板汇报数据老板想要的是这样的表一行一个月份每一行里分别有“下单数”、“完成数”、“取消数”三列。可是订单表里只有status字段区分状态怎么把一列拆成三列核心写法是用CASE在聚合函数内部做条件过滤。比如SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS total_cnt, COUNT(CASE WHEN status 已完成 THEN 1 END) AS finished_cnt, COUNT(CASE WHEN status 已取消 THEN 1 END) AS canceled_cnt FROM order_base GROUP BY DATE_FORMAT(create_time, %Y-%m);这段SQL的逻辑是这样的COUNT(CASE WHEN status 已完成 THEN 1 END)中case相当于一个过滤阀门——当行的状态是“已完成”时THEN 1让聚合函数对这一行计数当状态不是“已完成”时THEN后面的值没有被定义CASE返回NULL。COUNT函数在计算时只会统计非NULL值因此COUNT里面自然就只统计了“已完成”的行数。这个场景就是典型的“行转列”本质是利用CASE返回NULL或非NULL来控制聚合函数的统计范围。这里也可以写成SUM(CASE WHEN status已完成 THEN 1 ELSE 0 END)效果几乎一样只是用COUNT更简洁。COUNT(CASE...)和SUM(CASE...)的区别在于前者依赖“NULL不计数的特性”后者依赖“0不参与累加的特性”两者殊途同归按自己的习惯选择就好。2.3 条件聚合在同一个分组里计算多口径指标行转列只是条件聚合的一种表现。条件聚合更常见的使用场景是不拆列但是要在同一个分组内算多个指标比如“总订单数里大额订单占多少比例”。SELECT DATE_FORMAT(create_time, %Y-%m) AS month, COUNT(*) AS total_cnt, COUNT(CASE WHEN amount 500 THEN 1 END) AS big_order_cnt, ROUND( COUNT(CASE WHEN amount 500 THEN 1 END) / COUNT(*) * 100, 2 ) AS big_order_rate FROM order_base GROUP BY DATE_FORMAT(create_time, %Y-%m);这个用法在数据分析中极其常见总用户数、活跃用户数、付费用户数全部可以在同一个GROUP BY里算出来不用写多个子查询再JOIN。这里有一个细节要注意COUNT统计的是行数不是金额总和。如果你要按金额条件聚合比如“金额大于500的订单总金额”那就要用在SUM里配合CASESUM(CASE WHEN amount 500 THEN amount ELSE 0 END) AS big_order_amount2.4 分段逻辑对连续数值做区间归类有一类业务需求是给连续变量分类比如用户的年度消费金额分为“普通用户”、“中级用户”、“高级用户”、“VIP用户”。用搜索case写区间判断特别直观SELECT user_id, total_amount, CASE WHEN total_amount 1000 THEN 普通用户 WHEN total_amount 5000 THEN 中级用户 WHEN total_amount 20000 THEN 高级用户 ELSE VIP用户 END AS user_level FROM user_stat;这里有个容易被忽略的细节分支顺序很重要。搜索case是从上往下匹配的第一个满足条件的分支就会命中。所以区间判断里要么全部用区间下限判断并从小到大排列如 1000、 5000、 20000要么从大到小排列配合下限判断。千万不要写成交叉重叠的区间否则后面的分支永远不会被命中而且很难排查。比如WHEN total_amount 1000 THEN A和WHEN total_amount 5000 THEN B同时出现那金额6000的会永远走第一个分支。这个写法的好处是它可以在不修改表结构的前提下在查询结果里动态生成一个分类字段直接用于后续的报表分组。比如你就想按用户等级统计人数可以在子查询里先生成user_level外层再GROUP BY user_level非常方便。2.5 数据清洗与维度归并把散乱的值规整成标准类别实际业务数据往往又散又乱特别是渠道来源、支付方式这类维度字段会有很多相近但又不完全一样的取值。比如线上渠道里有的是“小程序”有的是“App”还有“H5”如果直接按字段分组报表会非常碎。用case归并维度字段是数据清洗阶段最常用的手段SELECT CASE WHEN channel IN (小程序, App, H5) THEN 线上 WHEN channel IN (门店, 专柜) THEN 线下 ELSE 其他渠道 END AS channel_group, COUNT(*) AS order_cnt FROM order_base GROUP BY CASE WHEN channel IN (小程序, App, H5) THEN 线上 WHEN channel IN (门店, 专柜) THEN 线下 ELSE 其他渠道 END;注意到一个细节这里GROUP BY后面没有用别名而是重复写了一遍完整的case表达式。这是因为MySQL虽然在某些时候允许GROUP BY接SELECT别名但为了严谨和兼容性在GROUP BY里重复case是更稳妥的做法。当然也可以更简洁地写成SELECT CASE WHEN channel IN (小程序, App, H5) THEN 线上 WHEN channel IN (门店, 专柜) THEN 线下 ELSE 其他渠道 END AS channel_group, COUNT(*) AS order_cnt FROM order_base GROUP BY channel_group;这种写法在MySQL里是允许的但在严格模式下部分版本可能不友好。如果你不确定线上环境的MySQL版本建议用第一种完整表达式的写法。归并维度的思路本质上是“把多样性收敛为标准口径”在数据仓库和BI报表建设中会频繁用到。3. 综合实战案例订单状态统计与渠道行转列为了让你串起前面所有的知识点我准备用一个完整的案例走一遍。这个案例虚构自一个电商业务的后台报表需求表结构和数据都是演示用途但逻辑真实可落地。假设有一张订单表order_base结构如下CREATE TABLE order_base ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, channel VARCHAR(16) COMMENT 渠道小程序/App/H5/门店, amount DECIMAL(10,2) NOT NULL, status VARCHAR(16) COMMENT 状态待支付/已完成/已取消, create_time DATETIME NOT NULL );业务方提了一个需求今天需要出一份日报按渠道统计订单情况要求一行一个渠道包含总订单数、已完成订单数、已完成订单金额、已完成率并按渠道横向对比。直接上最终SQLSELECT CASE WHEN channel IN (小程序, App, H5) THEN 线上 WHEN channel 门店 THEN 线下 ELSE 未知 END AS channel_group, COUNT(*) AS total_cnt, COUNT(CASE WHEN status 已完成 THEN 1 END) AS finished_cnt, SUM(CASE WHEN status 已完成 THEN amount ELSE 0 END) AS finished_amount, ROUND( COUNT(CASE WHEN status 已完成 THEN 1 END) / COUNT(*) * 100, 2 ) AS finished_rate FROM order_base GROUP BY CASE WHEN channel IN (小程序, App, H5) THEN 线上 WHEN channel 门店 THEN 线下 ELSE 未知 END;我们来逐段拆解这段SQL第一部分是CASE归并渠道维度把三个线上渠道合并为“线上”门店归为“线下”其他值归为“未知”。这一步解决了渠道维度过碎的问题。第二部分是COUNT(CASE WHEN status 已完成 THEN 1 END)这就是前面讲过的条件计数只统计已完成订单数。第三部分是SUM(CASE WHEN status 已完成 THEN amount ELSE 0 END)计算已完成订单的金额总和。第四部分是完成率计算。这里有个新手很容易踩的坑如果直接写COUNT(CASE...) / COUNT(*)得到的结果是整数不是百分比。MySQL中两个整数相除结果是浮点数所以理论上可以直接做小数的除法但为了保证精度的可控性我还是建议习惯性地在外面套ROUND(..., 2)并且注意分母不能为0。如果你的业务表在某个渠道下一个订单都没有那COUNT(*)就是0直接除会报除零警告并返回NULL。这种情况下可以用IFNULL包裹一下IFNULL(ROUND(COUNT(CASE WHEN status 已完成 THEN 1 END) / NULLIF(COUNT(*), 0) * 100, 2), 0)NULLIF(COUNT(*), 0)的作用是如果COUNT(*)为0把它转换为NULL这样除法结果就是NULL外层IFNULL再把它显示为0。这一段是整个案例里最需要仔细品的部分因为除零问题在报表开发中太常见了。执行出来的结果大概长这样channel_grouptotal_cntfinished_cntfinished_amountfinished_rate线上32629845210.0091.41线下74558875.5074.32这个案例虽然字段是虚构的但查询结构是完全可以直接搬到真实业务里的。如果业务方又要“按天按渠道”看趋势你只需要把GROUP BY改成DATE_FORMAT(create_time, %Y-%m-%d)再加上渠道分组即可核心的CASE逻辑不用动。4. 最容易踩的坑case用法排查实录case写起来简单但真正在复杂SQL里用起来坑也不少。这一节我把自己这些年见过的、踩过的坑整理成了一份排查速查表并挑重点展开讲。每一条都是真实出现过的教训。常见问题现象原因解决方案漏写ELSE导致数据少算聚合结果比预期少未匹配的行返回NULL聚合函数不计NULL写每条CASE时显式加ELSECASE后表达式与NULL比较NULL行不进入任何分支NULL NULL不成立用WHEN 字段 IS NULLTHEN分支类型不一致结果出现奇怪数字或字符串MySQL隐式转换统一THEN分支返回的类型在WHERE中使用CASE包裹索引列查询变慢索引失效CASE表达式破坏了原始列改写为OR/AND组合直接比较分支顺序错误部分区间永远不命中搜索case从上往下匹配按区间顺序严格排列CASE里嵌套子查询/函数过重查询性能明显下降每行都执行子查询先JOIN或提取公共表表达式第一个坑漏写ELSE这是最常见的。我见过一个统计需求CASE WHEN status 已完成 THEN amount END结果因为表中存在少量“已取消”状态所有已取消订单的金额都被记成了NULLSUM计算后金额凭空少了几万块。排查了很长时间才定位到是漏写ELSE 0导致的。所以我的习惯是每条CASE都写ELSE哪怕确定不会走到也显式写ELSE NULL这样至少后续维护的人知道你是有意处理的。第二个坑CASE后面的表达式与NULL比较。前面提过CASE NULL WHEN NULL THEN 1 END不会匹配这在做空值分类时很容易出错。比如你要给用户打标签没填写手机号的用户应该归类为“无手机号”如果你写CASE mobile WHEN NULL THEN 无手机号 ...是无效的因为NULL NULL的判断结果不是真。必须写CASE WHEN mobile IS NULL THEN 无手机号 ...。第三个坑THEN分支返回类型不一致。比如一个CASE里有的分支返回字符串有的分支返回数字MySQL会做隐式转换结果可能不是你要的。最典型的是CONCAT(CASE WHEN a 1 THEN 金额: ELSE amount END)当ELSE分支是数字时MySQL会试图把字符串和数字做合并如果数字那边不是纯数字字符串可能报错或者结果里出现“0”、“NULL”这种奇怪拼接。保持每个分支的类型一致是一个好习惯。第四个坑在WHERE条件里用CASE包裹索引列。比如你想表达“如果订单金额大于1000就看状态否则看渠道”有些人会写WHERE CASE WHEN amount 1000 THEN status ELSE channel END 已完成。这个写法语法上没问题但数据库无法对这个表达式使用索引只能全表扫描。正确的做法是改写成逻辑等价但可以走索引的OR组合WHERE (amount 1000 AND status 已完成) OR (amount 1000 AND channel 已完成);这里的规律是在WHERE里直接对裸字段做比较索引才能生效任何把字段包在函数或表达式里的行为都会让索引退化。第五个坑分支顺序混乱。这个我在分段逻辑部分讲过最典型的错误是WHEN amount 1000 THEN 大和WHEN amount 500 THEN 中同时出现金额800的订单明明应该分类为“中”却走进了“大”分支。解决办法是区间条件严格按大小顺序排列并养成从小到大或从大到小书写、逐段覆盖的习惯。第六个坑性能问题。CASE里如果嵌了子查询MySQL会针对每一行去执行一次子查询数据量一大性能直线下降。比如CASE WHEN (SELECT ...) 100 THEN ...这种写法除非确认子查询结果非常小且有索引支撑否则建议先把子查询结果JOIN进来或者用公共表表达式提前算好再在CASE里直接引用结果字段。5. 我的一些使用习惯与调试心得最后分享几个我在实际项目里形成的习惯不一定是最优解但都是在踩过坑之后沉淀下来的实用经验。第一个习惯是每条CASE都写ELSE。不管业务方说“这些值都已经覆盖了”我还是会写一个ELSE兜底。因为数据是动态的今天没有的状态明天可能就出现了。有了ELSE至少结果里能看到“未知”或“其他”而不是静默的NULL这能省下大量排查数据异常的时间。第二个习惯是开发阶段先用SELECT验证CASE分支再写进UPDATE或INSERT。有一个我印象很深的教训某次我给线上用户表打等级标签直接写了一条UPDATE ... SET level CASE WHEN ... END结果有个分支条件写反了导致一批高净值用户的等级被错误地改成了“普通用户”。恢复数据的过程非常痛苦。后来我养成了一个习惯凡是CASE要用于UPDATE或INSERT都会先在SELECT里把CASE的结果字段查出来对着数据量做一遍抽检确认每个分支都命中了预期数据再动手更新。这个习惯看起来多了一步操作却能在关键时刻避免灾难。第三个习惯是分支超过四五个的时候考虑用映射表代替CASE。比如状态码有20种每种对应一个中文名称那直接在SQL里写20个WHEN分支维护起来非常痛苦新加一个状态就得改SQL。这种情况更适合建一张状态码映射表用JOIN关联新增状态只需要在表里加一行。判断“用CASE还是用映射表”的经验法则是分支少、规则个性化强、只在一个查询里用它就用CASE分支多、规则统一、多个报表都要用就用映射表。第四个习惯是优先使用搜索case而不是简单case。就算只是简单的码值翻译搜索case的WHEN status 1也比CASE status WHEN 1更清晰而且在以后需要增加范围条件时可以直接扩展不需要改语法结构。最后一个实用技巧CASE可以和GROUP_CONCAT配合实现比行转列更灵活的输出。比如你想把每个用户的已支付订单号和已取消订单号分别拼成字符串输出到两列里SELECT user_id, GROUP_CONCAT(CASE WHEN status 已支付 THEN order_no END) AS paid_orders, GROUP_CONCAT(CASE WHEN status 已取消 THEN order_no END) AS canceled_orders FROM order_base GROUP BY user_id;GROUP_CONCAT会跳过NULL值所以CASE返回NULL的订单号不会拼进字符串里。这个组合在做用户维度的一行摘要时非常好用。MySQL的case表达式是一个越用越顺的利器它在SQL里提供了一种“在数据行内部做逻辑分支”的能力。你不需要为了一个简单的条件判断去写一堆子查询也不用在业务代码里循环处理数据。先把两种语法吃透再把那几个实战场景练熟日常SQL开发里遇到“依赖某个字段值做不同处理”的需求时你自然就会想到它。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
