SQL如何实现复杂的按部门层级汇总_递归与GROUP BY结合思路-SQL-PHP中文网

正确做法是先用WITH RECURSIVE生成“部门→所有下级”的完整映射（含root_id），再对外层结果GROUP BY root_id汇总；递归内不可聚合，JOIN须在递归后、分组前完成。

sql如何实现复杂的按部门层级汇总_递归与group by结合思路

用 `WITH RECURSIVE` 展开部门树，不是先 GROUP BY 再递归

递归查询必须在聚合前完成层级展开，否则 GROUP BY 会把子部门和父部门当成独立行处理，根本无法累加下级数据。常见错误是写成“先查出所有部门再递归”，结果只算了一层或报错 recursive reference must be in FROM clause。

正确做法是：用 WITH RECURSIVE 先生成完整的“部门→其所有下级”映射关系（每行是某部门和它的一个直接或间接下属），再对这个中间结果做 GROUP BY department_id 汇总。

起始查询（anchor）选所有顶层部门（parent_id IS NULL 或等于自身）
递归部分（recursive term）必须 JOIN 原表，且 ON 条件要匹配 parent_id = 上一层的 id
别名必须一致，且递归查询中不能出现聚合函数、DISTINCT、GROUP BY

WITH RECURSIVE dept_tree AS (
  SELECT id, id AS root_id FROM departments WHERE parent_id IS NULL
  UNION ALL
  SELECT d.id, dt.root_id
  FROM departments d
  INNER JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT dt.root_id, SUM(e.salary) AS total_salary
FROM dept_tree dt
JOIN employees e ON e.dept_id = dt.id
GROUP BY dt.root_id;

`GROUP BY` 要对 root_id 分组，不是原始部门 ID

如果按原始 dept.id 分组，就只是普通部门统计；而按递归中生成的 root_id 分组，才能让“销售部”的工资包含它下面“华东销售组”“华南销售组”的全部员工。

容易踩的坑是漏掉 root_id 字段，或者在递归 CTE 里没把它传下来——比如只写了 SELECT id，没写 SELECT id, root_id，导致外层无法分组。

递归 CTE 的列数和类型必须完全一致，root_id 类型要和 id 一致（比如都是 INT）
如果部门表有自环（比如 parent_id = id），递归会死循环，需加 MAXRECURSION（SQL Server）或 search depth 限制（PostgreSQL）
MySQL 8.0+ 支持 WITH RECURSIVE，但 5.7 不支持，别在旧环境硬套

汇总字段来自关联表时，JOIN 位置很关键

员工工资要算进“所属部门及其所有上级”，就必须在递归展开后 JOIN employees，而不是在递归前 JOIN。否则每个部门只关联自己的员工，下级数据就丢了。

典型错误写法：WITH RECURSIVE ... SELECT * FROM dept_tree JOIN employees ON ... —— 这样 JOIN 是在递归内部做的，语法都不合法。

递归 CTE 只负责生成路径关系，不碰业务数据
所有聚合计算（SUM、COUNT）必须放在 CTE 外层
如果要统计“每个部门下的员工数+下级部门数”，得用两个 CTE 分别展开员工和部门，或用条件聚合区分来源

性能差？检查索引和递归深度

部门层级深（比如 10 层以上）、节点多（几千个部门）时，递归 CTE 可能变慢，不是写法错，而是缺少支撑索引或未限制深度。

PostgreSQL 中 EXPLAIN ANALYZE 会显示递归节点执行次数，如果远超预期（比如 1000 行输入产生 5 万次 loop），大概率是 JOIN 条件没走索引。

确保 departments.parent_id 有索引
MySQL 8.0 默认递归上限 1000 层，超限报错 Recursive query aborted after 1001 iterations，需设 cte_max_recursion_depth
SQL Server 要显式加 OPTION (MAXRECURSION 32767)，否则默认只跑 100 层

真正麻烦的是跨库兼容：SQLite 支持 WITH RECURSIVE 但不支持窗口函数配合；Oracle 用 CONNECT BY，语法完全不同。一个 SQL 想通吃所有数据库，基本做不到。