SQL如何实现按层级递归分组查询_利用递归CTE技巧-SQL-PHP中文网

PostgreSQL中WITH RECURSIVE做树形分组统计需先展开层级再聚合，不可在递归项中使用GROUP BY或聚合函数；MySQL需调大cte_max_recursion_depth；SQL Server hierarchyid适合路径查询但分组不如CTE直观；Oracle建议用WITH RECURSIVE替代CONNECT BY。

sql如何实现按层级递归分组查询_利用递归cte技巧

PostgreSQL 里用 `WITH RECURSIVE` 做树形结构分组统计

递归查询不是“加个关键字就能跑”，得先理清层级起点和聚合边界。常见错误是把 GROUP BY 直接套在递归 CTE 外层，结果只统计了叶子节点，父节点的汇总值全丢了。

正确做法是：先用递归 CTE 把整棵树展开成扁平路径（带 level、path、parent_id），再对外层结果按 level 或祖先 ID 分组聚合。

WITH RECURSIVE tree AS ( SELECT id, parent_id, name, 1 AS level FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name, t.level + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT level, COUNT(*) FROM tree GROUP BY level;
如果要算每个父节点下子孙总数，得在递归中保留根 ID：SELECT root_id, COUNT(*) FROM ( ... SELECT id AS root_id, id FROM ... UNION ALL SELECT t.root_id, c.id FROM ... ) t GROUP BY root_id;
别在递归分支里写 GROUP BY 或聚合函数——会报错：ERROR: aggregate functions are not allowed in a recursive query's recursive term

MySQL 8.0+ 怎么写等价的递归分组（不用存储过程）

MySQL 的 WITH RECURSIVE 语法和 PostgreSQL 接近，但默认递归深度限制为 1000，超深树会直接中断并报错：ERROR 3636 (HY000): Recursive query aborted after 1001 iterations。

必须提前调大限制，且注意：这个设置是 session 级的，不能只靠建表或查询时声明。

执行前先设：SET SESSION cte_max_recursion_depth = 5000;
递归体里避免 JOIN 多张表，否则容易触发隐式笛卡尔积，让递归爆炸式膨胀
MySQL 不支持在递归项中引用外层查询的列（比如动态传入起始 id），得用变量或预处理语句兜底
分组统计建议放在递归结束后的主查询里，例如：SELECT ancestor_id, COUNT(*) FROM (WITH RECURSIVE r AS (...) SELECT t.id AS ancestor_id, c.id FROM r t JOIN categories c ON c.path LIKE CONCAT(t.path, '%')) g GROUP BY ancestor_id;

SQL Server 的 `hierarchyid` 比 CTE 更适合层级分组？

不是“更适合”，而是适用场景不同。hierarchyid 是物理存储优化，对“查某节点所有后代”极快，但对“按层级深度分组统计”反而绕——得先用 GetLevel() 提取深度，再 GROUP BY，不如 CTE 直观。

真正省事的是它自带路径比较能力，比如快速筛选第 3 层所有节点：WHERE orgnode.GetLevel() = 3，不用拼字符串或自定义函数。

用 hierarchyid 必须确保列已建索引，否则 GetAncestor() 或 IsDescendantOf() 会变慢
导入旧数据时，hierarchyid::Parse() 容易因格式错误失败，比如 /1/2//3/ 多斜杠就报 SqlDateTime overflow
跨数据库迁移时，hierarchyid 是 SQL Server 特有类型，PostgreSQL/MySQL 无法识别，CTE 方案兼容性高得多

Oracle 11g 以后怎么避开 CONNECT BY 写递归分组

能避开就避开。CONNECT BY 语法僵硬，不支持标准 SQL 的窗口函数嵌套，想在递归过程中算累计数或排名，基本得套两层子查询，可读性差还容易出错。

11g 起其实支持 WITH RECURSIVE（需开启 COMPATIBLE 参数 ≥ 11.2），但很多人不知道或不敢开——因为担心影响老应用。真要稳妥，还是得用 START WITH ... CONNECT BY，只是分组逻辑必须后置。

错误写法：SELECT LEVEL, COUNT(*) FROM dept START WITH parent_id IS NULL CONNECT BY parent_id = PRIOR id GROUP BY LEVEL; —— 这会把每条路径单独计一次，不是按层级聚合
正确思路：先 SELECT id, LEVEL lvl FROM dept START WITH ... CONNECT BY ... 得到带层级的中间集，再包一层 SELECT lvl, COUNT(*) FROM (...) GROUP BY lvl
CONNECT_BY_ROOT 可以标记每行所属根节点，适合做“每个部门下员工数”这类分组，但注意它不能出现在递归条件里，否则报 ORA-01436: CONNECT BY loop in user data

递归分组最常被忽略的一点：没有预先剪枝的树，哪怕只有几百个节点，也可能因环路或重复引用导致查询卡死或返回脏数据。上线前务必用 COUNT(*) 和 LIMIT（或 TOP）先试跑递归展开结果，确认路径数量合理再加聚合。