如何在SQL中利用递归CTE视图生成组织架构树状层级结构？-SQL-PHP中文网

sql server递归cte必须用无recursive关键字的with+union all结构，with须为首词，锚与递归成员列数类型顺序一致，并需显式引用自身、加option(maxrecursion n)防自环。

如何在sql中利用递归cte视图生成组织架构树状层级结构？

SQL Server 里不能用 WITH RECURSIVE，抄 MySQL/PostgreSQL 写法直接报错；必须用无 RECURSIVE 关键字的 WITH + UNION ALL 结构，且锚和递归部分顺序、引用关系不能错。

它不认 WITH RECURSIVE，只认 WITH 开头、UNION ALL 连接、递归支中必须显式引用自身 CTE 名——三者缺一不可。

UNION 写成 UNION ALL：漏掉 ALL 会导致去重逻辑干扰树形结构，子节点重复时数据丢失
递归支里没写 JOIN Tree ON ... 或写成 JOIN Employees ON ...：必须出现 Tree（你定义的 CTE 名），否则 SQL Server 不认为这是递归
锚查询没加 WHERE parent_id IS NULL 或 WHERE id = @rootId：会从所有根节点开始展开，生成多棵树混在一起的结果
WITH 没放在批处理最前面：比如套在 IF 块里却前面有注释或空行，会报 “Incorrect syntax near 'WITH'”

光查出父子关系不够，前端或报表要缩进、排序、识别深度，得靠 Level 和 Path 字段。这两个必须在 CTE 里算好，别指望外层再加工。

Level 从 1 开始，每次递归 + 1；避免用 0 起始，否则和 OPTION (MAXRECURSION n) 的默认行为容易混淆
Path 推荐用 CONCAT('/', id) 起步，递归支用 CONCAT(t.Path, '/', e.id)，保证可排序、可截断、可做 LIKE 查询
如果表主键是字符串（如部门编码 'DEPT-001'），Path 字段必须用定长字符类型（如 VARCHAR(500)），否则超长被截断后路径失效
别在 CTE 里用 REPLICATE('─', Level * 2) 拼前缀——那是展示层逻辑，CTE 只管结构，留到最终 SELECT 再算

CTE 不是变量，也不是函数，它没有作用域嵌套概念，但有严格的语句位置约束。在存储过程里写错位置，比在 SSMS 里还容易翻车。

WITH 必须是 IF 分支里的第一条可执行语句：下面这样会失败：

IF @rootId IS NOT NULL
BEGIN
    -- 注释或空行在这里就违规
    WITH Tree AS (...)
    SELECT * FROM Tree;
END

动态根节点别拼 SQL：用参数驱动锚查询，例如 WHERE id = @rootId，而不是 EXEC('WITH Tree AS (...) SELECT * FROM Tree WHERE id = ' + @rootId) —— 动态 SQL 无法复用执行计划，且易注入
深度控制要外显：默认 MAXRECURSION 100 很容易不够，尤其组织架构超 6 层或评论嵌套深；必须在最终 SELECT 后加 OPTION (MAXRECURSION 500)，且不能写在 CTE 定义里
别在外层再套一层 SELECT * 包装 CTE：除非真要 ORDER BY Path 或分页，否则纯增解析开销，SQL Server 不会优化掉这层包装