SQL如何实现分组结果的层级化展示和树状结构聚合？

递归CTE不能在内部使用GROUP BY或聚合函数，必须先展开树形结构再在外层查询中聚合；常见错误是递归部分误加SUM()或GROUP BY，正确做法是CTE只负责层级展开，聚合逻辑移至外层JOIN后执行。

递归 CTE 不能直接聚合，必须把 GROUP BY 和聚合函数（如 SUM()、COUNT()）放到外层查询里——这是最常踩的坑，一写错就报错或结果翻倍。

递归 CTE 内部禁止用 GROUP BY 或 SUM()

递归 CTE 的作用是展开树形关系（比如所有子孙节点），它输出的是扁平行集，每行代表一个节点+其某一级祖先/后代。数据库不允许在递归定义块里加 GROUP BY，因为递归过程行数动态变化，聚合语义不成立。

常见错误写法：

WITH RECURSIVE tree AS (  SELECT id, parent_id, sales FROM orgs WHERE parent_id IS NULL  UNION ALL  SELECT p.id, p.parent_id, SUM(c.sales)  -- ❌ 错误：递归支里不能用 SUM()  FROM orgs p  JOIN tree c ON p.id = c.parent_id  GROUP BY p.id, p.parent_id  -- ❌ 错误：递归支里不能用 GROUP BY)

正确做法是：先让 CTE 完整展开树（含层级、路径等字段），再在外层 JOIN 业务表并 GROUP BY。

外层聚合时怎么关联才能算“本级 + 所有子类”？

关键在 JOIN 条件——不是关联到当前节点，而是关联到 CTE 展开后每一行所代表的“归属关系”。

• 若 CTE 输出的是每个节点的所有后代（向下展开），则用 ON t.id = orders.category_id：这样每个订单会匹配到它所属分类及其所有上级，自然实现“子类销量计入父类”
• 若 CTE 输出的是每个节点的所有祖先（向上展开），则用 ON t.ancestor_id = orders.category_id，效果相同
• 想只统计“本级自身”，得额外加 WHERE t.level = 0 或类似条件，但这已脱离树形汇总目的

别漏掉索引：orders.category_id 上必须建索引，否则外层 JOIN 可能全表扫描，深树下性能断崖式下跌。

ROLLUP 和递归 CTE 不是一个东西，别混用

GROUP BY ... WITH ROLLUP 是按列顺序做逐级归并（如 GROUP BY region, dept WITH ROLLUP 生成 region/dept、region/NULL、NULL/NULL 三档），它不理解树结构，也不依赖 parent_id 字段。它适合固定维度的报表小计，不适合组织架构、商品类目这种动态深度的树。

典型误用：

• 把 category_id 放在 GROUP BY 第一位，导致顶层小计变成“全部类目总和”，中间层（如“一级类目总和”）反而没了
• 指望 ROLLUP 自动识别父子关系——它不会，它只认列顺序

真要分层展示又带缩进，得靠递归 CTE 里拼 REPEAT(' ', level) 或生成 path 字符串，ROLLUP 做不到。

深度超限和无限递归是两个不同风险点

MySQL 默认 @@cte_max_recursion_depth = 1000，SQL Server 默认 100 层，PostgreSQL 无硬限制但可能 OOM。但真正致命的不是“树太深”，而是“写反 JOIN 条件”或“漏终止条件”：

• ON t.id = r.parent_id 写成 ON t.parent_id = r.id → 无限循环
• 锚点没过滤根节点（如漏 WHERE parent_id IS NULL）→ 初始集为空，整个 CTE 返回空
• 递归支没限制 WHERE r.parent_id IS NOT NULL → 某些数据 parent_id=0 或 -1，导致死循环

上线前务必用 SELECT * FROM your_cte LIMIT 20 快速验证是否收敛，别等查生产库卡住才反应过来。