MySQL三表左连接实现分类菜单商品计数查询（含All分类与0值统计）

现有逻辑的优化方向 #

你目前调试通过的启用分类计数SQL已经完全覆盖4项基础统计规则，仅缺少独立计算的All分类行。之前使用UNION补充时计数为9的问题，本质是计算All行时没有显式限定启用商品范围，误将1个未启用的商品纳入了统计结果。

完整可运行SQL #

-- 补充All分类行：独立统计所有启用商品总数
SELECT 
  0 AS category_id,
  'All' AS category_name,
  COUNT(product_id) AS product_count,
  0 AS sort_flag -- 控制All行排在结果最前端
FROM _product
WHERE enabled = 1

UNION ALL

-- 复用你已验证正确的启用分类计数逻辑
SELECT 
  c.category_id AS category_id,
  c.name AS category_name,
  COUNT(p.product_id) AS product_count,
  1 AS sort_flag
FROM _category c
LEFT JOIN _category_to_product c2p 
  ON c.category_id = c2p.category_id
LEFT JOIN _product p 
  ON c2p.product_id = p.product_id 
  AND p.enabled = 1
WHERE c.enabled = 1
GROUP BY c.category_id, c.name

-- 排序规则：先展示All行，其余分类按名称升序排列
ORDER BY sort_flag, category_name ASC;

逻辑匹配校验 #

对照你提出的5条统计规则，调整后的SQL可完全覆盖要求：

• 未启用的分类（如测试数据中的Sheetmetal）被WHERE c.enabled = 1条件过滤，不会出现在最终结果中
• 未启用的商品（如测试数据中的Christmas）因为关联了p.enabled = 1的连接条件，不会被计入任何分类的统计结果
• 单个商品可属于多个分类的场景，因为统计维度是单个分类左连接关联表，会将商品分别计入每个对应分类的计数，不会出现漏算或重复统计
• 没有关联任何商品的启用分类（如Rocks、Pencil），因为采用左连接逻辑，COUNT(p.product_id)会自动返回0值，完全符合计数要求
• All分类的计数直接从商品表统计所有处于启用状态的记录，包含未关联任何分类的商品（如Puppies）、关联未启用分类的商品（如Horses），同时排除所有未启用商品，最终计数为8，和预期结果完全一致

实现优势 #

• 没有改动你已经调试通过的原有分类统计逻辑，最大程度避免引入新的计数偏差
• All分类的计算逻辑完全独立，和分类关联表没有耦合，不会因为关联关系的变化影响总数准确性
• 用UNION ALL替代UNION，因为两段查询的结果没有重复行（All分类的id为0，和真实分类的主键id不会冲突），不需要额外的去重开销，性能更优

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