第 10 章条件逻辑与CASE WHEN

站长2026-10-23 12:00:00

内容摘要

上一篇我们认识了 IF、IFNULL 这些简单的流程控制函数，但只要分支超过两层，它们就会变得难读难维护。本篇专门讲 SQL 里最强大的条件表达工具 `CASE WHEN`，以及它与聚合函数结合后完成"行转列"报表的经典套路。学完后你能写出多分支分类、动态排序、交叉报表等复杂查询。本示例基于 MySQL 8.0 语法。

📖 本篇简介：上一篇我们认识了 IF、IFNULL 这些简单的流程控制函数，但只要分支超过两层，它们就会变得难读难维护。本篇专门讲 SQL 里最强大的条件表达工具 CASE WHEN，以及它与聚合函数结合后完成"行转列"报表的经典套路。学完后你能写出多分支分类、动态排序、交叉报表等复杂查询。本示例基于 MySQL 8.0 语法。

1 条件逻辑在SQL中的地位

编程语言里有 if/else，SQL 里对应的就是条件逻辑。它让数据库不只是"把数据捞出来"，而是"按业务规则现场加工数据"。比如：

用户消费 1 万以上标 VIP，1 千以上标会员，其余标普通；
订单状态 1 显示待付款、2 已发货、3 已完成；
报表里把"每个月每个地区的销量"横向铺开成一行。

这些都不是单纯的字段映射，而是"判断 + 赋值"。CASE WHEN 就是 SQL 里的 if/else if/else。它可以用在 SELECT、WHERE、ORDER BY、GROUP BY 里，是把业务规则写进查询的关键。

对比一下"在应用层判断"和"在 SQL 层判断"的差异：

| 做法 | 优点 | 缺点 |

|---|---|---|

| 应用层 if/else | 业务逻辑集中、好调试 | 要把全量数据拉到应用层，网络开销大 |

| SQL 里 CASE WHEN | 数据不落地就完成加工、口径统一 | 复杂规则塞进 SQL 可读性下降 |

经验是：简单映射、分类、报表聚合放 SQL；复杂业务流程、需要事务一致性的逻辑放应用层。本篇聚焦 SQL 这一侧。

2 CASE WHEN基本语法

CASE WHEN 有两种形态，先看最通用的"搜索 CASE"：

-- CASE WHEN 基本语法（MySQL 8.0）
SELECT
  user_id,
  total_amount,
  CASE
    WHEN total_amount >= 10000 THEN 'VIP'      -- 满足条件返回该值
    WHEN total_amount >= 1000  THEN '会员'
    ELSE '普通'                                 -- 都不满足走 ELSE
  END AS level                                  -- END 结束，起别名
FROM user_stat;

要点：

1. CASE 开头、END 结尾，缺一不可；

2. 每个 WHEN 条件 THEN 结果 是一个分支，从上往下匹配，命中即停；

3. ELSE 可选——不写的话，所有分支都不命中时返回 NULL；

4. 它本身是一个表达式，可以出现在任何表达式能出现的地方。

把状态码翻译成可读文案是最常见的用途：

-- 订单状态翻译（MySQL 8.0）
SELECT
  order_id,
  status,
  CASE status
    WHEN 1 THEN '待付款'
    WHEN 2 THEN '已发货'
    WHEN 3 THEN '已完成'
    WHEN 4 THEN '已取消'
    ELSE '未知'
  END AS status_text
FROM orders;

2.1 分支里写复合条件

WHEN 后面可以是任意布尔表达式，支持 AND/OR、函数、子查询。比如"年龄在 18~60 且消费超过 500"才标活跃用户：

-- 复合条件分支（MySQL 8.0）
SELECT
  user_id,
  CASE
    WHEN age BETWEEN 18 AND 60 AND total_amount > 500 THEN '活跃用户'
    WHEN age < 18 OR age > 60                       THEN '非主力人群'
    ELSE '普通用户'
  END AS user_tag
FROM user_info;

注意 WHEN 条件是"短路"的：从上到下依次判断，命中第一个满足的分支就停止，后面不再判断。这和大多数编程语言的 if/else if 一致。

3 简单CASE与搜索CASE两种写法

上面第二个例子就是"简单 CASE"：CASE 字段 WHEN 值 THEN ...，它把字段直接写在 CASE 后面，只做等值比较。

| 写法 | 结构 | 适用 |

|---|---|---|

| 简单 CASE | CASE 字段 WHEN 常量 THEN ... | 字段与多个固定值等值比较 |

| 搜索 CASE | CASE WHEN 条件 THEN ... | 任意复杂条件（范围、多字段、表达式） |

两者对比：

-- 写法一：简单 CASE（只能等值）（MySQL 8.0）
SELECT CASE level WHEN 1 THEN '低' WHEN 2 THEN '中' WHEN 3 THEN '高' END FROM t;

-- 写法二：搜索 CASE（可写范围）（MySQL 8.0）
SELECT CASE
         WHEN score >= 90 THEN '优秀'
         WHEN score >= 60 THEN '及格'
         ELSE '不及格'
       END
FROM exam;

经验法则：能一眼看出是"同一个字段对几个固定值做映射"时用简单 CASE，代码短；只要涉及范围、多字段组合、AND/OR，就直接用搜索 CASE。混用反而增加阅读负担。

举个对比例子，同样是"按金额分级"：

-- 错误示范：简单 CASE 做不到范围判断（MySQL 8.0）
SELECT CASE total_amount
         WHEN 10000 THEN 'VIP'   -- 这只匹配恰好等于 10000 的行
         WHEN 1000  THEN '会员'  -- 同样只匹配恰好 1000
         ELSE '普通'
       END
FROM user_stat;

上面这种写法是错的——简单 CASE 是等值比较，total_amount=10001 的用户不会被算成 VIP。范围分级必须用搜索 CASE：

-- 正确：搜索 CASE 才能表达范围（MySQL 8.0）
SELECT CASE
         WHEN total_amount >= 10000 THEN 'VIP'
         WHEN total_amount >= 1000  THEN '会员'
         ELSE '普通'
       END
FROM user_stat;

3.1 CASE 与 ORDER BY：动态排序

CASE WHEN 放在 ORDER BY 里可以实现"按业务优先级排序"：把 VIP 排前面，会员次之，普通最后，同等级再按金额降序。

-- 动态排序（MySQL 8.0）
SELECT user_id, level, total_amount
FROM user_stat
ORDER BY
  CASE level
    WHEN 'VIP'  THEN 1
    WHEN '会员' THEN 2
    ELSE 3
  END,
  total_amount DESC;

3.2 CASE 出现在其他位置

CASE 是表达式，理论上 SELECT 里能写表达式的地方都能用。几个高频位置：

-- CASE 放在 GROUP BY 里：按"评级"分组统计（MySQL 8.0）
SELECT
  CASE
    WHEN total_amount >= 10000 THEN 'VIP'
    WHEN total_amount >= 1000  THEN '会员'
    ELSE '普通'
  END AS level,
  COUNT(*) AS user_cnt
FROM user_stat
GROUP BY
  CASE
    WHEN total_amount >= 10000 THEN 'VIP'
    WHEN total_amount >= 1000  THEN '会员'
    ELSE '普通'
  END;

GROUP BY 里重复写一遍 CASE 是因为它不能直接引用 SELECT 里的别名（部分数据库支持，但 MySQL 标准写法建议照写，或用子查询包一层）。把它放进 HAVING 也常见：

-- HAVING 里过滤（MySQL 8.0）
SELECT region, SUM(amount) AS total
FROM orders
GROUP BY region
HAVING SUM(CASE WHEN amount>=200 THEN 1 ELSE 0 END) > 0;  -- 只要有大额订单的地区

4 IF与IFNULL与NULLIF函数

上一篇已介绍过这三个函数，这里集中对比，因为它们和 CASE WHEN 是"能互相替代"的关系。

-- 三个函数对比（MySQL 8.0）
SELECT
  IF(score >= 60, '及格', '不及格')     AS r1,  -- 单条件二选一
  IFNULL(phone, '未绑定')              AS r2,  -- 空值兜底
  NULLIF(divisor, 0)                   AS r3;  -- 相等返回 NULL

| 函数 | 等价 CASE | 用途 |

|---|---|---|

| IF(c,a,b) | CASE WHEN c THEN a ELSE b END | 单分支二选一 |

| IFNULL(a,b) | CASE WHEN a IS NULL THEN b ELSE a END | NULL 兜底 |

| NULLIF(a,b) | CASE WHEN a=b THEN NULL ELSE a END | 除零保护、空串转 NULL |

当逻辑只有一个分支时，用 IF 比 CASE WHEN 简洁；一旦出现第二个分支，就果断换成 CASE WHEN，不要再写 IF(...) 套 IF(...) 的嵌套地狱。

4.1 什么时候用哪个

| 分支数 | 推荐写法 |

|---|---|

| 只判断空值 | IFNULL |

| 单条件二选一 | IF |

| 两三个固定值映射文案 | 简单 CASE |

| 范围判断、多字段组合 | 搜索 CASE |

| 聚合里按条件统计 | SUM(CASE WHEN ... END) |

记住一个信号：当你发现自己在写 IF(a, IF(b, x, y), z) 这种两层以上嵌套时，就是该换成 CASE WHEN 的信号。

5 条件聚合与行转列

这是 CASE WHEN 最有威力的应用：把它塞进聚合函数里，按"条件"汇总，从而实现"行转列"——把原本纵向排列的多行数据，横向铺成一行多列。

5.1 基础：按条件统计

需求：统计每个地区里"大额订单（金额≥200）"和"小额订单"各有多少。

-- 条件聚合（MySQL 8.0）
SELECT
  region,
  COUNT(*)                                            AS total,      -- 全部订单数
  SUM(CASE WHEN amount >= 200 THEN 1 ELSE 0 END)     AS big_cnt,    -- 大额订单数
  SUM(CASE WHEN amount < 200  THEN 1 ELSE 0 END)     AS small_cnt   -- 小额订单数
FROM orders
GROUP BY region;

原理：CASE 在每一行上判断，命中返回 1，否则返回 0；SUM 把这些 1/0 加起来，就得到"满足条件的行数"。等价的另一种写法是 COUNT(CASE WHEN amount>=200 THEN 1 END)——注意 COUNT 里 ELSE 不写，不命中时返回 NULL，COUNT 自动跳过 NULL，效果相同。

5.2 实战：行列转换报表

经典交叉表：行是地区，列是每个月的销售额。原始数据是 orders(region, month, amount) 一行一笔订单，报表要求一行显示各月数据。

-- 行转列（MySQL 8.0）
SELECT
  region,
  SUM(CASE WHEN month = '2026-01' THEN amount ELSE 0 END) AS m1,
  SUM(CASE WHEN month = '2026-02' THEN amount ELSE 0 END) AS m2,
  SUM(CASE WHEN month = '2026-03' THEN amount ELSE 0 END) AS m3
FROM orders
GROUP BY region;

用 Python + sqlite3 实跑验证这个逻辑，确认行列转换结果：

# sqlite3 实跑验证条件聚合/行转列（Python 3.14 + sqlite3 3.53）
import sqlite3
con = sqlite3.connect(':memory:')
cur = con.cursor()
cur.executescript('''
CREATE TABLE sales(region TEXT, amount INT);
INSERT INTO sales VALUES ('East',100),('East',200),('West',150),('West',50),('East',300);
''')
rows = cur.execute('''
SELECT region,
       SUM(amount) AS total,
       SUM(CASE WHEN amount>=200 THEN amount ELSE 0 END) AS big_amt,
       COUNT(CASE WHEN amount>=200 THEN 1 END) AS big_cnt
FROM sales GROUP BY region;
''').fetchall()
for r in rows:
    print(r)
con.close()

实跑输出：

('East', 600, 500, 2)
('West', 200, 0, 0)

East 地区三笔订单 100+200+300=600，其中≥200 的是 200 和 300，合计 500、共 2 笔；West 两笔都不足 200，大额统计为 0。逻辑完全正确。

5.3 条件聚合的几个变体

| 需求 | 写法 |

|---|---|

| 统计满足条件的行数 | SUM(CASE WHEN cond THEN 1 ELSE 0 END) |

| 满足条件的金额求和 | SUM(CASE WHEN cond THEN amount END) |

| 满足条件的平均值 | AVG(CASE WHEN cond THEN score END) |

| 不命中时不参与平均 | AVG(CASE WHEN cond THEN score END)（ELSE 省略，NULL 被跳过） |

5.4 经典交叉报表完整例子

把上面所有技巧拼成一个完整报表：行是地区，列是 Q1~Q4 的销售额，最后一列合计。

-- 季度交叉报表（MySQL 8.0）
SELECT
  region,
  SUM(CASE WHEN quarter=1 THEN amount ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter=2 THEN amount ELSE 0 END) AS q2,
  SUM(CASE WHEN quarter=3 THEN amount ELSE 0 END) AS q3,
  SUM(CASE WHEN quarter=4 THEN amount ELSE 0 END) AS q4,
  SUM(amount) AS year_total
FROM orders
WHERE year = 2026
GROUP BY region
ORDER BY year_total DESC;

这种报表在 BI 报表、月度总结里天天见。理解了"CASE 过滤行、聚合函数做汇总"这个组合，所有交叉表都是一个套路。

5.5 条件更新：一条 SQL 批量改值

CASE 还能写在 UPDATE 的 SET 里，实现"按字段值批量映射更新"，避免写一堆 UPDATE ... WHERE ...：

-- 批量调整折扣（MySQL 8.0）
UPDATE goods
SET discount = CASE category
                  WHEN '电子' THEN 0.9
                  WHEN '服装' THEN 0.8
                  WHEN '食品' THEN 0.95
                  ELSE 1.0          -- 其他品类不打折
                END;

注意 ELSE 1.0 一定要写，否则未命中的行 discount 会被更新成 NULL，把原来的值覆盖掉。这是条件更新最容易犯的错。

5.6 算转化率：条件聚合的延伸

运营最关心"转化率"，本质就是"满足后续条件的行数 / 总行数"：

-- 注册到下单转化率（MySQL 8.0）
SELECT
  COUNT(*)                                              AS reg_cnt,    -- 注册用户数
  SUM(CASE WHEN has_order=1 THEN 1 ELSE 0 END)         AS order_cnt,  -- 下过单的用户数
  ROUND(SUM(CASE WHEN has_order=1 THEN 1 ELSE 0 END)/COUNT(*)*100, 2) AS conv_pct  -- 转化率%
FROM user;

这类"分母是全量、分子是满足条件子集"的查询，是条件聚合最常见的业务形态。

6 条件逻辑速查表

| 场景 | 推荐写法 |

|---|---|

| 二选一 | IF(c,a,b) |

| 字段空值兜底 | IFNULL(col,'默认') |

| 多分支分类 | CASE WHEN ... THEN ... ELSE ... END |

| 字段值映射文案 | 简单 CASE col WHEN v THEN ... END |

| 动态排序 | ORDER BY CASE ... END |

| 条件计数 | SUM(CASE WHEN c THEN 1 ELSE 0 END) |

| 行转列 | SUM(CASE WHEN 维度=值 THEN 指标 END) |

| 除零保护 | 分母 / NULLIF(分母,0) |

7 常见坑与注意事项

1. 漏写 END：CASE 必须以 END 收尾，少一个就语法报错。报错信息往往指到语句末尾，回头检查 CASE 是否闭合。

2. ELSE 省略导致 NULL：忘了写 ELSE，又有不命中的行，结果列里冒出一堆 NULL。要么写 ELSE 兜底，要么业务上就接受 NULL。

3. 分支顺序敏感：CASE 从上往下匹配，命中即停。把"范围大的条件"写在前面会让后面的分支永远不命中。比如先写 WHEN score>=60 再写 WHEN score>=90，90 分也会被归到"及格"。

4. CASE 里写聚合、聚合里写 CASE 搞混：CASE 出现在聚合函数内部做条件统计，和在 HAVING 里对聚合结果再判断，是两回事。先想清楚"条件作用在行级还是组级"。

5. 行转列写死月份：报表月份是动态的，硬编码 m1/m2/m3 每加一个月就要改 SQL。生产环境常借助存储过程或应用层动态拼 SQL 生成列。

6. 用 IF 嵌套超过两层：可读性差且容易漏条件，分支一多立刻迁到 CASE WHEN。

7.1 常见错误写法对照

| 错误写法 | 问题 | 正确思路 |

|---|---|---|

| CASE score WHEN score>=60 | 简单 CASE 里又写条件，逻辑错位 | 改用搜索 CASE |

| CASE WHEN a>10 THEN '大' WHEN a>5 THEN '中' | 范围大的在前，a=8 也被归"大" | 从小到大排列分支 |

| SUM(CASE WHEN c THEN 1 ELSE 0)/COUNT(*) | 想算占比 | 写成 AVG(CASE WHEN c THEN 1 ELSE 0) 更直观 |

| CASE col WHEN NULL THEN '空' | 等值比 NULL 永远不成立 | 用 CASE WHEN col IS NULL |

最后一点很关键：NULL = NULL 在 SQL 里结果是"未知"而非真，所以简单 CASE 里 WHEN NULL 永远匹配不上，判断空值必须写 IS NULL。

8 练习题

练习 1：分数评级

表 exam(user_id, score)，查出每个用户分数和评级：≥90 优秀、≥60 及格、否则不及格。

-- 分数评级（MySQL 8.0）
SELECT user_id, score,
  CASE
    WHEN score >= 90 THEN '优秀'
    WHEN score >= 60 THEN '及格'
    ELSE '不及格'
  END AS grade
FROM exam;

</details>

练习 2：统计各状态订单数

表 orders(status)，一条 SQL 查出待付款、已发货、已完成各多少单。

-- 各状态订单数（MySQL 8.0）
SELECT
  SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) AS unpaid,
  SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) AS shipped,
  SUM(CASE WHEN status=3 THEN 1 ELSE 0 END) AS done
FROM orders;

</details>

练习 3：动态排序

用户表有 is_vip（0/1）和 login_time，要求 VIP 用户排前面，普通用户按最近登录时间倒序。

-- VIP 优先排序（MySQL 8.0）
SELECT user_id, is_vip, login_time
FROM user
ORDER BY
  CASE is_vip WHEN 1 THEN 0 ELSE 1 END,
  login_time DESC;

</details>

练习 4：空值归类

表 orders 的 coupon_id 可能为 NULL，要求查询时把 NULL 显示为"未用券"。

-- NULL 显示为文案（MySQL 8.0）
SELECT order_id,
  IFNULL(CAST(coupon_id AS CHAR), '未用券') AS coupon
FROM orders;

</details>

练习 5：行列转换

表 sales(region, quarter, amount)，查出每个地区 Q1、Q2、Q3、Q4 及全年合计。

-- 季度交叉表（MySQL 8.0）
SELECT region,
  SUM(CASE WHEN quarter=1 THEN amount ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter=2 THEN amount ELSE 0 END) AS q2,
  SUM(CASE WHEN quarter=3 THEN amount ELSE 0 END) AS q3,
  SUM(CASE WHEN quarter=4 THEN amount ELSE 0 END) AS q4,
  SUM(amount) AS total
FROM sales
GROUP BY region;

</details>

本章小结

CASE WHEN 是 SQL 里少有的"越用越上瘾"的功能：一旦掌握条件聚合，很多原本要在应用层循环拼接的报表，一条 SQL 就出结果。记住两个核心：分支从上往下匹配、聚合里套 CASE 就是条件统计。下一篇进入性能优化的起点——索引。

下一篇预告：第 11 章索引基础与创建

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 10 章条件逻辑与CASE WHEN

目录

1 条件逻辑在SQL中的地位

2 CASE WHEN基本语法

2.1 分支里写复合条件

3 简单CASE与搜索CASE两种写法

3.1 CASE 与 ORDER BY：动态排序

3.2 CASE 出现在其他位置

4 IF与IFNULL与NULLIF函数

4.1 什么时候用哪个

5 条件聚合与行转列

5.1 基础：按条件统计

5.2 实战：行列转换报表

5.3 条件聚合的几个变体

5.4 经典交叉报表完整例子

5.5 条件更新：一条 SQL 批量改值

5.6 算转化率：条件聚合的延伸

6 条件逻辑速查表

7 常见坑与注意事项

7.1 常见错误写法对照

8 练习题

练习 1：分数评级

练习 2：统计各状态订单数

练习 3：动态排序

练习 4：空值归类

练习 5：行列转换

本章小结

文章推广海报

文章回复

第 10 章 条件逻辑与CASE WHEN

目录

1 条件逻辑在SQL中的地位

2 CASE WHEN基本语法

2.1 分支里写复合条件

3 简单CASE与搜索CASE两种写法

3.1 CASE 与 ORDER BY：动态排序

3.2 CASE 出现在其他位置

4 IF与IFNULL与NULLIF函数

4.1 什么时候用哪个

5 条件聚合与行转列

5.1 基础：按条件统计

5.2 实战：行列转换报表

5.3 条件聚合的几个变体

5.4 经典交叉报表完整例子

5.5 条件更新：一条 SQL 批量改值

5.6 算转化率：条件聚合的延伸

6 条件逻辑速查表

7 常见坑与注意事项

7.1 常见错误写法对照

8 练习题

练习 1：分数评级

练习 2：统计各状态订单数

练习 3：动态排序

练习 4：空值归类

练习 5：行列转换

本章小结

第 10 章条件逻辑与CASE WHEN