如何在SQL中利用条件聚合代替多表子查询以优化性能？-SQL-PHP中文网

CASE WHEN + SUM能干掉相关子查询，因将多次扫描压缩为一次全表扫描，避免N次重复计算和临时表IO；需注意补ELSE 0、不提前WHERE过滤、字段类型匹配；跨表取明细字段时必须用JOIN。

如何在sql中利用条件聚合代替多表子查询以优化性能？

为什么`CASE WHEN` + `SUM`能干掉相关子查询？

因为数据库不用为每一行反复执行一次子查询，也不用建临时表。它把“对每个用户统计付费次数”和“统计总金额”这两个动作，压进一次全表扫描里完成——本质上是用空间换时间，但这次换得值：避免了N次重复计算和临时表IO。

`SUM(CASE WHEN ... THEN 1 ELSE 0 END)`写法要注意什么？

这个模式本质是条件聚合，但容易踩三个坑：

别漏ELSE 0：没有ELSE时，不满足条件的行会变成NULL，而SUM(NULL)结果仍是NULL，不是0
别在WHERE里提前过滤掉目标行：比如想统计“已付款订单数”，却写WHERE status = 'paid'，那其他状态的订单就彻底进不了聚合范围，失去对比意义
聚合字段类型要匹配：比如用COUNT统计非空值更安全，但若必须用SUM，确保THEN分支返回数值型，别混入字符串

什么时候该坚持用`JOIN`而不是条件聚合？

条件聚合适合“同一张表内按不同规则分组统计”，一旦涉及跨表取明细字段（比如订单表要带用户昵称、商品类目名），就必须用JOIN。强行用子查询拉取这些字段，就会触发N+1查询——10万订单，就要额外执行10万次SELECT nickname FROM users WHERE id = ?。

此时正确路径是：

先用LEFT JOIN一次性关联用户表、商品表
再在SELECT里用CASE WHEN做条件计数或求和
确保JOIN字段（如user_id）有索引，否则关联本身就成了瓶颈

真实慢查询改写示例：从子查询到条件聚合

原始语句（慢）：

SELECT 
  u.id,
  u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id AND o.status = 'paid') AS paid_cnt,
  (SELECT SUM(amount) FROM orders o WHERE o.user_id = u.id AND o.status = 'paid') AS paid_amt
FROM users u;

问题：对每个用户执行两次子查询，users表每多一行，就多两次全表扫描orders（或索引扫描）。

优化后（快）：

SELECT 
  u.id,
  u.name,
  COUNT(CASE WHEN o.status = 'paid' THEN 1 END) AS paid_cnt,
  COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.amount END), 0) AS paid_amt
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

关键点：COALESCE(..., 0)防SUM返回NULL；GROUP BY必须包含SELECT中所有非聚合字段；LEFT JOIN保证没订单的用户也能出现。

真正难的不是写对这句SQL，而是意识到：当你要从主表每行“动态算一个值”时，先别急着写子查询——先问自己，这个值能不能被拆解成“条件+聚合”，再塞进一次扫描里算完。