简化MySQL查询：移除WHERE子句中的多SELECT语句

Hey Drewy, 我完全懂你现在的困扰——一堆嵌套的子查询不仅让SQL代码变得臃肿难读，还会让数据库反复扫描表，更关键的是网页端要接收多组查询结果，数据收发量和响应速度都受影响。咱们可以用条件聚合来彻底重构这个统计查询，一次性搞定所有许可证状态的计数，既简化代码又提升性能。

先举个常见的原查询场景（假设你的代码是这类结构） #

比如原来的查询可能是这样，每个状态都用一个子查询：

SELECT
  (SELECT COUNT(*) FROM work_permits WHERE status = 'active') AS active_count,
  (SELECT COUNT(*) FROM work_permits WHERE status = 'expired') AS expired_count,
  (SELECT COUNT(*) FROM work_permits WHERE status = 'pending') AS pending_count,
  (SELECT COUNT(*) FROM work_permits WHERE status = 'revoked') AS revoked_count;

简化后的优化方案：用条件聚合一次性统计 #

只需要扫描一次数据表，就能把所有状态的计数都算出来，返回一行结果给网页端，大大减少数据传输量：

SELECT
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
  COUNT(CASE WHEN status = 'expired' THEN 1 END) AS expired_count,
  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
  COUNT(CASE WHEN status = 'revoked' THEN 1 END) AS revoked_count,
  COUNT(*) AS total_count -- 可选，如果你需要统计总许可证数量
FROM work_permits;

或者用SUM函数也能实现同样的效果，逻辑是一致的：

SELECT
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
  SUM(CASE WHEN status = 'expired' THEN 1 ELSE 0 END) AS expired_count,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
  SUM(CASE WHEN status = 'revoked' THEN 1 ELSE 0 END) AS revoked_count,
  COUNT(*) AS total_count
FROM work_permits;

为什么这个方案更好？ #

• 性能提升：原查询要反复扫描work_permits表N次（N是状态数量），优化后只扫描1次，数据库计算开销大幅降低
• 减少数据收发：原查询返回的是多个子查询的结果集合并，优化后只返回一行数据，网页端接收的数据量直接缩水
• 代码易维护：后续要新增状态统计，只需要加一行CASE WHEN即可，不用再写新的子查询

如果需要分组统计？ #

比如要按部门、年份等维度分组统计许可证状态，直接在查询里加GROUP BY就行，完全兼容：

SELECT
  department,
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
  COUNT(CASE WHEN status = 'expired' THEN 1 END) AS expired_count,
  COUNT(*) AS total_count
FROM work_permits
WHERE issue_date >= '2023-01-01' -- 可选的时间过滤条件
GROUP BY department;

如果你的原查询还有关联其他表、更复杂的过滤逻辑，可以把完整的原SQL贴出来，我能帮你调整出更贴合场景的优化版本~

内容的提问来源于stack exchange，提问作者Drewy