因多次COUNT(*)配合不同WHERE会触发多次全表扫描,I/O与开销翻倍;应改用CASE WHEN内联聚合,单次扫描完成多路计数,如COUNT(CASE WHEN status='paid' THEN 1 END)。

为什么不能对同一张表写多个 COUNT(*) 配合不同 WHERE?
因为那样会触发多次全表扫描,I/O 和执行计划开销翻倍。数据库优化器通常不会自动把 COUNT(*) WHERE condition1 和 COUNT(*) WHERE condition2 合并成一次扫描——它默认按语句粒度独立优化。
真正高效的做法是:用 CASE WHEN 把条件逻辑“内联”进聚合函数里,让 SUM 或 COUNT 在单次遍历中完成多路计数。
-
COUNT(CASE WHEN status = 'paid' THEN 1 END)统计已支付订单数(NULL不计入) -
SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END)累加已支付金额(ELSE 0避免SUM跳过NULL导致结果偏小) - 所有
CASE表达式共享同一行数据上下文,扫描一次即可产出全部指标
CASE WHEN + COUNT vs CASE WHEN + SUM:选哪个?
关键看你要的是「满足条件的行数」还是「带权重的累加值」。二者语义不同,混用会导致结果错误。
- 统计人数、订单数、异常条数等离散计数 → 用
COUNT(CASE WHEN ... THEN 1 END),THEN后必须是非NULL值(1最安全),ELSE可省略(默认为NULL,不被COUNT计入) - 统计金额、时长、评分总和等连续累加 → 用
SUM(CASE WHEN ... THEN amount ELSE 0 END),ELSE 0必须显式写出,否则遇到不匹配行会变成NULL,导致整列SUM结果为NULL - 别写
COUNT(CASE WHEN ... THEN amount END)——这统计的是amount非空且满足条件的行数,不是金额总和
GROUP BY 场景下嵌套 CASE WHEN 的常见坑
当需要按地区、日期等维度分组,并在每组内做多条件聚合时,CASE WHEN 必须写在聚合函数内部,而不是 GROUP BY 子句里。
- ✅ 正确:
SELECT region, COUNT(CASE WHEN score >= 90 THEN 1 END) AS high_score FROM students GROUP BY region - ❌ 错误:
SELECT region, CASE WHEN score >= 90 THEN 'A' END AS grade FROM students GROUP BY region, grade—— 这会强制按grade分组,破坏原始分组粒度,且无法聚合 - 聚合字段里不能出现未被
GROUP BY包含的裸列(如score),但CASE WHEN是表达式,不受此限 - 如果
CASE中引用了需要聚合的字段(比如MAX(price)),需确保该字段本身已在GROUP BY中或被外层聚合包裹
性能和可读性兼顾的写法建议
复杂多维聚合容易写成一行超长 SQL,既难调试也难复用。实际开发中更推荐结构化写法:
- 把每个
CASE WHEN拆成带别名的子表达式,例如:COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped_cnt - 避免在
CASE中写嵌套函数(如DATE_FORMAT(create_time, '%Y-%m')),先用子查询或 CTE 提前计算好衍生字段,再聚合 - 注意 NULL 处理一致性:所有分支都要覆盖,尤其是
ELSE分支;线上数据常有隐式NULL,漏掉会导致聚合结果静默丢失 - PostgreSQL 和 SQL Server 支持
FILTER语法(如COUNT(*) FILTER (WHERE status = 'paid')),语义更清晰,但 MySQL 不支持,跨库迁移时要留意
多维聚合真正的难点不在语法,而在明确每个指标的业务定义是否互斥、是否允许重叠、NULL 值代表什么含义——这些决定了 CASE 的条件边界和默认分支怎么写,而不仅是“会不会用”。










