怎样利用SQL关联查询生成复杂树形报表_结合PATH和Recursive CTE

WITH RECURSIVE 是处理动态深度树形结构的唯一可靠方案，因 JOIN 仅支持固定层级、无法生成可排序路径、易漏数据且不兼容聚合与索引；其必须包含锚点和递归体两部分，顺序不可颠倒。

MySQL 8.0+ 中，用 WITH RECURSIVE + 路径拼接（CONCAT/CAST）就能直接生成带完整层级路径的树形报表，无需应用层组装，也避免多次查询。

为什么不能只用 JOIN 处理树形路径？

普通自连接最多支持固定层数（比如三级部门），一旦层级动态变化或深度超过预设，结果就漏数据；而且路径字段（如 "1/2/4"）必须靠应用拼接，报表 SQL 本身无法输出可排序、可截断的结构化路径。

• JOIN 写法在深度不确定时逻辑爆炸，5 层就要写 5 次 LEFT JOIN departments d2 ON d1.id = d2.parent_id
• 没有原生层级计数，level 字段得靠 CASE 或变量模拟，不可靠且不兼容 GROUP BY
• 路径字符串无法参与索引优化，WHERE 条件查“所有二级子节点”会全表扫描

WITH RECURSIVE 必须包含的两个部分

递归 CTE 不是“可选语法糖”，而是强制分两块：锚点（起点）和递归体（自我引用）。缺一不可，顺序不能颠倒，否则 MySQL 直接报错 ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY 或 Recursive reference to CTE 'xxx' not allowed。

• 锚点查询：必须返回至少一行，且不能引用自身 CTE 名。例如 SELECT id, name, parent_id, 0 AS level, CAST(id AS CHAR(100)) AS path FROM departments WHERE parent_id IS NULL
• 递归查询：必须用 UNION ALL 连接，且 FROM 子句中必须显式 JOIN 到该 CTE 自身（如 JOIN dept_tree dt ON d.parent_id = dt.id）
• 禁止在递归部分使用 GROUP BY、ORDER BY、聚合函数（COUNT/SUM），否则报错

生成可排序的层级路径（如 "1.2.3" 或 "总公司/技术部/后端组"）

关键不是拼字符串，而是让路径具备**字典序即层级序**的特性——这样 ORDER BY path 就天然按树形展开顺序排列，LIKE '1.2.%' 就能秒查整个子树。

• 用 CONCAT(dt.path, '.', d.id) 拼数字编号路径，注意初始 CAST(id AS CHAR(100)) 防止隐式类型转换失败
• 用 CONCAT(dt.path, '/', d.name) 拼中文路径时，确保 path 字段定义为 VARCHAR(500) 以上，避免被截断
• 如果需要补零对齐（如 "001.002.004"），改用 LPAD(d.id, 3, '0')，但会牺牲路径可读性
• 路径字段别名必须在 CTE 定义头显式声明，例如 WITH RECURSIVE dept_tree (id, name, path, level) AS (...)

报表中常被忽略的性能与安全细节

递归查询跑得慢或查不出数据，90% 出在三个地方：循环引用没切断、层级过深未限制、路径字段长度不足。

• 必须加 MAX_RECURSION_DEPTH 控制，否则脏数据（如 A→B→C→A）会导致无限循环。在语句开头加 SET SESSION cte_max_recursion_depth = 50;
• 路径字段若用 CHAR(10)，三层之后就溢出，后续 CONCAT 返回 NULL，整条分支消失——查不到子节点往往是因为这个
• 想统计每层人数？别在递归 CTE 里 COUNT(*)，先查出完整树形结果到临时表，再外层 GROUP BY level
• EXPLAIN FORMAT=TREE 能看到递归执行计划，但注意它不显示实际递归次数，需靠 SELECT COUNT(*) 验证结果行数是否符合预期

真正难的不是写出第一版递归 SQL，而是当业务要求“查某节点下所有三级以内部门，且路径只显示前两级名称”时，能否快速拆解成锚点条件 + 递归终止条件 + 路径截断逻辑——这需要把路径当作一等公民来设计，而不是最后补的字符串。