case when能避免多次扫描表，因为它在单次行遍历中完成所有条件判断与值映射，共享同一行上下文；而多个where查询需重复全表扫描，i/o开销倍增。
为什么CASE WHEN能避免多次扫描表
因为数据库执行时，CASE WHEN是在单次遍历行的过程中完成条件判断和值映射，所有分支共享同一行数据上下文。如果用多个WHERE子句分别查不同条件再UNION ALL或JOIN，就会触发多次全表扫描——尤其在大表上，I/O开销直接翻倍。
CASE WHEN + 聚合函数的标准写法
核心是把条件逻辑“内嵌”进聚合函数参数里，让SUM、COUNT等只对匹配的行累加，其余返回NULL或0（注意COUNT会忽略NULL，SUM则需配合COALESCE防空）：
SELECT COUNT(*) AS total, SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count, SUM(CASE WHEN amount > 100 THEN amount ELSE 0 END) AS high_amount_sum, COUNT(CASE WHEN created_at >= '2024-01-01' THEN 1 END) AS new_users FROM orders;
-
COUNT里用CASE WHEN ... THEN 1 END，不写ELSE会让不满足条件的行返回NULL，从而被COUNT跳过 -
SUM建议显式写ELSE 0，否则NULL参与运算会导致整列结果为NULL - 所有
CASE分支必须返回相同类型，比如不能一个分支返回INT，另一个返回VARCHAR
容易踩的坑：NULL值与聚合行为错配
最常见错误是以为COUNT(CASE WHEN cond THEN col END)能统计非空值，其实它统计的是cond为真且col非NULL的行数——如果col本身可能为NULL，就会漏计：
-- ❌ 错误：当 score 是 NULL 时，即使 condition 为 true，COUNT 也不计入 COUNT(CASE WHEN subject = 'math' THEN score END) <p>-- ✅ 正确：只关心条件是否成立，与 score 是否 NULL 无关 COUNT(CASE WHEN subject = 'math' THEN 1 END)</p>
- 用
COUNT(1)或COUNT(*)替代COUNT(col)来规避字段空值干扰 - 若需条件求和且字段可能为空，先用
COALESCE(score, 0)兜底再套CASE - MySQL中
CASE无ELSE默认返回NULL；PostgreSQL和SQL Server同理，但某些旧版SQLite可能行为不同
复杂条件怎么写才清晰不爆炸
多层逻辑别堆在一个CASE里硬套WHEN，优先拆成独立表达式，用括号明确优先级：
-- ✅ 清晰：把复合条件提取为布尔表达式 SUM(CASE WHEN (status = 'shipped' AND days_since_order -- ❌ 模糊：嵌套太多，可读性差，也难调试 SUM(CASE WHEN status = 'shipped' THEN CASE WHEN days_since_order
- 超过3个
WHEN分支时，考虑用IN或范围判断简化，比如WHEN category IN ('A','B','C') - 涉及时间计算（如
DATEDIFF、EXTRACT）务必确认数据库时区和函数返回类型，避免隐式转换失败 - 在
GROUP BY后使用时，所有非聚合字段必须出现在GROUP BY中，CASE表达式也不例外
真正难的不是语法，而是想清楚“这一列我到底要统计什么”——条件边界模糊时，先手写几行测试数据跑一遍CASE逻辑，比对着执行计划猜快得多。
