SQL 中 CASE WHEN 的真实用途

CASE WHEN 的核心价值是行级条件驱动的字段生成或值映射，需写 ELSE 防 NULL 失真，条件顺序影响结果，适用于 GROUP BY 条件聚合、窗口函数标签标记等场景，避免在 JOIN 和 WHERE 中滥用。

用 CASE WHEN 做行级动态计算，不是为了“看起来像 if”

很多人写 CASE WHEN 是为了模仿编程语言的 if-else，结果写出一堆嵌套、可读性差还难维护的逻辑。它真正的价值在于：在单条 SQL 语句中，对每一行数据做**条件驱动的字段生成或值映射**，且不改变原始表结构。

典型场景包括：订单状态转义（'P' → 'pending'）、金额分级打标（amount > 1000 → 'high'）、多源渠道归一（source = 'web' 或 'app' 统一为 'online'）。

• 必须用 ELSE —— 没写 ELSE 时默认返回 NULL，容易导致聚合结果意外失真（比如 COUNT(*) 和 COUNT(CASE WHEN ...) 数量不一致）
• WHEN 子句按顺序匹配，第一个为 TRUE 的就返回对应 THEN 值，后续不再判断 —— 这意味着条件顺序很重要，比如把 score >= 90 放在 score >= 60 前面，否则高分永远进不了第一档
• 不能在 WHERE 子句里直接用列别名（如 SELECT CASE WHEN x>0 THEN 1 ELSE 0 END AS flag WHERE flag = 1 会报错），得重写条件或用子查询/CTE

在 GROUP BY 和聚合函数里用 CASE WHEN 实现条件统计

这是 CASE WHEN 最不可替代的用途：在一个 GROUP BY 查询中，同时算出多个维度的条件计数、求和或平均值，避免写多个子查询或 UNION。

例如统计每个部门中「薪资超 15k 的人数」和「平均工龄」，不需要拆成两条 SQL：

SELECT 
  dept,
  COUNT(CASE WHEN salary > 15000 THEN 1 END) AS high_salary_cnt,
  AVG(CASE WHEN join_year < 2020 THEN years_of_service END) AS avg_senior_exp
FROM employees
GROUP BY dept

• CASE WHEN ... THEN 1 END 中的 END 后不要跟 ELSE 0 —— 因为 COUNT() 只统计非 NULL 值，用 ELSE 0 反而会让所有行都被计入，失去条件过滤意义
• AVG() 会自动忽略 NULL，所以 CASE WHEN ... THEN x END 天然适配；但若写成 CASE WHEN ... THEN x ELSE 0 END，就把不该参与平均的记录拉低了结果
• 注意 NULL 安全：如果原始字段本身可能为 NULL（如 salary），salary > 15000 判断结果也是 NULL，不会进入任何 WHEN 分支 —— 这是符合三值逻辑的，但容易被忽略

避免在 JOIN 条件里滥用 CASE WHEN

ON 子句里用 CASE WHEN 做连接逻辑，多数时候是设计缺陷的信号。它会让执行计划变复杂，优化器难生成高效索引路径，甚至触发全表扫描。

PaperFake

AI写论文

下载

比如这种写法：

JOIN orders o ON o.user_id = CASE WHEN u.type = 'vip' THEN u.vip_id ELSE u.normal_id END

• 更合理的做法是提前把 user_id 标准化（加计算列或视图），或拆成两个 LEFT JOIN 再用 COALESCE() 合并结果
• 某些数据库（如 PostgreSQL）对 CASE 表达式中的列无法下推索引，MySQL 8.0+ 虽支持函数索引，但 CASE 不在支持列表里
• 如果只是想处理 NULL 匹配（如 ON a.id = b.ref_id OR (a.id IS NULL AND b.ref_id IS NULL)），直接用布尔逻辑比套一层 CASE 更清晰、更易优化

窗口函数 + CASE WHEN 是分析类查询的隐藏主力

当你要给每行打上「是否为当月首单」「是否连续登录第 N 天」「是否高于部门均值」这类标签时，CASE WHEN 和窗口函数组合几乎是唯一简洁解法。

例如标记每个用户最新一笔订单：

SELECT *,
  CASE WHEN rn = 1 THEN 'latest' ELSE 'old' END AS order_rank_label
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) t

• 窗口函数必须先于 CASE WHEN 计算完成，所以一定要套子查询或 CTE；直接在外部 SELECT 里写 ROW_NUMBER() OVER(...) = 1 是语法错误
• 注意排序字段的确定性：如果 created_at 有重复，ROW_NUMBER() 结果不稳定，可能导致同一笔订单有时被标为 'latest' 有时不是 —— 加上主键作为第二排序字段可解决
• CASE WHEN 在窗口后使用，不参与窗口计算本身，因此不会影响分区或排序逻辑，这点和放在 WHERE 或 GROUP BY 中完全不同