如何在SQL Server中获取员工完整的管理层级关系

你的递归CTE无法获取完整层级的核心问题有两个：

1. 递归成员的字段选择错误：递归时你错误地使用了关联表HR的ID_employee作为新的员工ID，导致递归过程中员工ID被替换成了上级的ID，无法追踪原始员工的完整层级链。
2. 自引用的终止逻辑不完善：最高负责人的ID_Manager等于自身，需要在递归时明确终止条件，避免无效循环或提前中断。

WITH ManagerHierarchy AS (
    -- 锚点成员：获取所有员工的直接上级
    SELECT 
        ID_employee, 
        ID_manager
    FROM HierarchyResource

    UNION ALL

    -- 递归成员：保留原始员工ID，向上追溯上级的上级
    SELECT 
        mh.ID_employee,  -- 保留原始员工ID，不替换
        hr.ID_manager    -- 取当前上级的上级
    FROM ManagerHierarchy mh
    INNER JOIN HierarchyResource hr 
        ON mh.ID_manager = hr.ID_employee
    -- 终止条件：当上级的上级等于自身时停止递归（针对最高负责人）
    WHERE hr.ID_manager != hr.ID_employee
)
SELECT ID_employee, ID_manager 
FROM ManagerHierarchy
-- 按员工ID倒序、管理层级从直接上级到最高层排序，匹配期望结果
ORDER BY ID_employee DESC, ID_manager DESC
OPTION (MAXRECURSION 10);

• 锚点成员：保留所有员工的直接上下级关系，作为递归的起点。
• 递归成员：
  • 用mh.ID_employee保留原始员工ID，确保每一条记录都属于同一个原始员工；
  • 用hr.ID_manager获取当前上级的上级，构建完整的层级链。
• 终止条件：hr.ID_manager != hr.ID_employee确保当递归到最高负责人（ID=1）时，因为其ID_manager等于自身，不会继续递归，避免无限循环。
• 排序：添加ORDER BY ID_employee DESC, ID_manager DESC让结果和你期望的格式完全匹配。

执行上述查询后，会得到你期望的完整管理层级关系：

内容的提问来源于stack exchange，提问作者Mr Alsi