SQL如何用SQL子查询实现多级分类统计_嵌套聚合分组-SQL-PHP中文网

子查询中可使用GROUP BY，但需满足非聚合字段全在GROUP BY中且子查询须加别名；否则MySQL 8.0+/PostgreSQL报ERROR 1055。

sql如何用sql子查询实现多级分类统计_嵌套聚合分组

子查询里不能直接用 GROUP BY 的聚合结果？

不是不能，而是多数人写法错了——外层没给子查询起别名，或者把聚合字段漏在了 SELECT 列表里但没出现在子查询的 GROUP BY 中。MySQL 8.0+ 和 PostgreSQL 要求：子查询若含 GROUP BY，其 SELECT 中所有非聚合字段都必须出现在 GROUP BY 子句里，否则报错 ERROR 1055。

正确做法是把分组统计逻辑封进带别名的子查询，再让外层查这个“虚拟表”：

SELECT c.name, t.cnt
FROM category c
JOIN (
  SELECT parent_id, COUNT(*) AS cnt
  FROM product
  GROUP BY parent_id
) t ON c.id = t.parent_id;

子查询必须加别名（比如 t），否则外层 JOIN 会报错 Every derived table must have its own alias
parent_id 是分组依据，也是关联外层分类表的关键字段，不能丢
如果要统计“本级分类下有多少子分类”，得换字段：子查询里 GROUP BY id，外层 ON c.parent_id = t.id

多级分类树怎么一层层算出各级商品数？

靠一次 JOIN 不够，得用递归 CTE（PostgreSQL/SQL Server/MySQL 8.0+）或自连接模拟层级。但注意：**嵌套聚合本身不解决层级遍历问题，它只解决“某一层已知 ID 集合下的聚合”**。真要从根到叶子逐级统计，得先拿到路径或深度。

常见错误是试图在子查询里用 WHERE parent_id IN (SELECT id FROM category WHERE level = 1) 去算一级分类的商品数——这没问题；但想靠同一子查询同时算出一级、二级、三级的数量，就会卡住。

稳妥做法：用 CTE 先展开层级关系（如 WITH RECURSIVE tree AS (...)），再对 tree 表 GROUP BY level, id
兼容老版本 MySQL（5.7）？用自连接最多支持固定层数，比如两级：先 JOIN category c2 ON c1.id = c2.parent_id，再对 c1.id 和 c2.id 分别聚合
性能敏感时避免在子查询里 JOIN 大表，优先让子查询只输出 ID + 统计值，外层再关联维度表

WHERE 条件该放子查询里还是外层？

取决于过滤目标：筛数据行，就放子查询内；筛聚合结果，就放外层 HAVING 或外层 WHERE。放错位置会导致结果少、多、甚至语法报错。

例如，要查“有超过 5 个商品的一级分类”，COUNT(*) > 5 必须写在外层或子查询的 HAVING 中，绝不能写成 WHERE COUNT(*) > 5（语法非法）：

SELECT c.name, t.cnt
FROM category c
JOIN (
  SELECT parent_id, COUNT(*) AS cnt
  FROM product
  WHERE status = 'on_sale'  -- ✅ 这里筛原始数据行
  GROUP BY parent_id
  HAVING COUNT(*) > 5       -- ✅ 这里筛分组结果
) t ON c.id = t.parent_id;

子查询内的 WHERE 越早过滤掉无效记录，性能越好（减少 GROUP BY 输入量）
外层 WHERE 适合用于关联后进一步筛选，比如 WHERE c.level = 1
千万别在子查询外层用 WHERE cnt > 5 却忘了子查询里没写 HAVING——虽然能跑，但语义模糊且可能漏掉本该被聚合排除的组

LEFT JOIN + 子查询统计时 NULL 怎么处理？

当分类下没商品，子查询不返回对应 parent_id，LEFT JOIN 后 cnt 是 NULL，不是 0。这是最常被忽略的细节。

别依赖外层 COALESCE(t.cnt, 0) 就完事——如果子查询因条件太严（比如 WHERE sale_date > '2024-01-01'）导致某些分类完全不出现在子查询结果中，LEFT JOIN 确实会补 NULL，但你要确认这是否符合业务定义：“无记录”等于“零个”，还是“暂无数据需排除”。

要强制补 0：用 COALESCE(t.cnt, 0)，但确保子查询没过度过滤（比如不该加的时间范围限制）
更安全的做法是在子查询里用 RIGHT JOIN 或先生成完整分类 ID 列表再左连统计结果
如果分类表本身有大量空父节点（parent_id IS NULL），子查询 GROUP BY parent_id 会把它们全归为一组，容易误统计——此时应显式区分 WHERE parent_id IS NOT NULL

实际写多级分类统计时，真正的复杂点不在语法嵌套，而在于“哪一级的 ID 是已知的、哪一级需要推导、哪些空值该保留哪些该转 0”。这些边界稍不厘清，数字就对不上。