sql server存储过程中递归cte必须将with置于begin...end块首行，且须用union all、加option(maxrecursion n)于最终select后，禁用union以防去重丢失节点。
存储过程里写递归CTE必须把WITH放在分支最开头
SQL Server 存储过程中计算组织架构提成，第一步就是查出某人下属所有层级节点。但很多人写完 IF @empId IS NOT NULL BEGIN SELECT ... ; WITH Tree AS (...) ... END 就报错：Incorrect syntax near the keyword 'WITH'。原因很直接——WITH 必须是所在作用域（比如 BEGIN...END 块）里的第一条可执行语句，前面不能有任何其他语句（包括 SELECT、DECLARE、注释都不行）。
正确写法是把整个递归逻辑前置：
IF @empId IS NOT NULL
BEGIN
WITH Tree AS (
-- 锚成员：自己
SELECT id, name, manager_id, 0 AS level, CAST(id AS VARCHAR(500)) AS path
FROM employees WHERE id = @empId
UNION ALL
-- 递归成员：找所有下级
SELECT e.id, e.name, e.manager_id, t.level + 1, t.path + ',' + CAST(e.id AS VARCHAR(10))
FROM employees e
INNER JOIN Tree t ON e.manager_id = t.id
)
SELECT * FROM Tree OPTION (MAXRECURSION 500);
END
- 别在
WITH前加DECLARE @level INT或空行，哪怕只是注释也得挪到BEGIN外面 - 如果要支持多根（比如查多个总监的团队），不要在一个 CTE 里塞
OR条件，拆成多个独立WITH块更稳 - MySQL 8.0+ 或 PostgreSQL 可用
WITH RECURSIVE，但 SQL Server 不认这个关键字，硬写会直接语法报错
提成计算必须用UNION ALL，不能用UNION
组织架构里常有重名员工（比如三个“王经理”），如果递归 CTE 里误用 UNION，SQL Server 虽不报语法错，但会在每层做去重，导致整条子树丢失。提成算出来少一半，还很难排查——因为数据看起来“合理”，只是漏了节点。
真正起作用的是 UNION ALL，它保证逐层追加，不丢数据：
-- ✅ 正确：保留所有同名节点 SELECT id, name, salary, 0 AS depth FROM employees WHERE id = @root UNION ALL SELECT e.id, e.name, e.salary, t.depth + 1 FROM employees e INNER JOIN Tree t ON e.manager_id = t.id
-
UNION引入隐式SORT算子，执行计划里能看到额外排序开销，拖慢深层查询 - 检查执行计划时，若递归分支出现
Sort或Hash Match (Aggregate)，基本可断定误用了UNION - 提成逻辑常需按层级加权（如直属下级提成10%，二级5%），用
UNION ALL才能拿到完整depth字段用于后续计算
OPTION(MAXRECURSION n)必须加在最终SELECT末尾
查一个200人的销售团队，层级深度可能达12层；但 SQL Server 默认只允许100层递归。一旦超限，不是返回部分结果，而是直接报错：The maximum recursion 100 has been exhausted，且整个存储过程中断，调用方收不到任何数据。
这个限制必须显式解除，且只能加在最终输出语句后：
SELECT
t.id,
t.name,
t.salary,
CASE t.depth
WHEN 0 THEN 0
WHEN 1 THEN t.salary * 0.1
ELSE t.salary * 0.05
END AS bonus
FROM Tree t
OPTION (MAXRECURSION 500); -- ✅ 正确位置
-
OPTION (MAXRECURSION 0)表示不限制，但生产环境禁用——数据里存在循环引用（A→B→A）会导致会话卡死、内存爆满 - 建议把最大深度设为输入参数，例如
@maxDepth INT = 10，调用时传CALL CalcBonus @empId = 123, @maxDepth = 8，避免硬编码 - MySQL 8.0+ 对应的是
SET SESSION cte_max_recursion_depth = 500，不能写在 CTE 内部，也不能用OPTION
缩进显示和路径拼接要用REPLICATE或STRING_AGG
提成报表常需展示树形结构（比如“马总 → 张工 → 李助理”），而不是只返回扁平ID列表。靠多次 LEFT JOIN 自关联模拟层级，代码冗长、性能差、最多撑不过4层。
最轻量可靠的方式是用 REPLICATE 拼缩进，或用 STRING_AGG 拼路径：
SELECT
REPLICATE(' ', t.depth) + t.name AS display_name,
STRING_AGG(t.name, ' → ') WITHIN GROUP (ORDER BY t.depth) AS full_path,
t.bonus
FROM Tree t
GROUP BY t.id, t.name, t.depth, t.bonus;
-
REPLICATE(' ', t.depth)比SPACE(t.depth * 2)更明确，避免空格被前端截断 -
STRING_AGG是 SQL Server 2017+ 支持的，老版本可用FOR XML PATH('')替代，但注意特殊字符转义问题 - 别在递归 CTE 内部就做
STRING_AGG——CTE 只负责展开层级，聚合留到外层 SELECT，否则执行计划会退化
实际写提成逻辑时，最易忽略的是循环引用检测和深度控制粒度。组织表里偶尔存在脏数据（比如员工 A 的 manager_id 指向自己，或 A→B→C→A），仅靠 OPTION (MAXRECURSION) 不够，得在锚成员加 WHERE manager_id IS NOT NULL AND id != manager_id 过滤，否则第一层就崩。
