如何编写递归SQL查询获取员工工时审批人（支持多层汇报）

需要编写SQL查询提取员工信息，包含工时卡审批人。数据库中存储员工的汇报对象（Reports to），但该对象不一定具备审批权限。仅Super和PM职位拥有工时卡审批权限，需持续向上追溯汇报链，直到找到具备审批权限的对象作为审批人，且不能使用临时表，避免依赖创建权限。

原始员工数据 #

期望输出 #

解决方案：递归CTE查询 #

递归CTE（Common Table Expression）是解决层级追溯问题的标准方案，无需临时表。以下是适配需求的SQL代码：

WITH RECURSIVE EmployeeApproval AS (
    -- 锚点成员：筛选所有具备审批权限的员工作为初始节点
    SELECT 
        EENum, 
        EENum AS ApprovedBy,
        Position
    FROM Employees
    WHERE Position IN ('Super', 'PM')
    
    UNION ALL
    
    -- 递归成员：关联无审批权限的员工，继承上级的审批人
    SELECT 
        e.EENum, 
        ea.ApprovedBy,
        e.Position
    FROM Employees e
    INNER JOIN EmployeeApproval ea ON e.[Reports to] = ea.EENum
    WHERE e.Position NOT IN ('Super', 'PM')
)
-- 最终输出：按员工号排序
SELECT EENum, ApprovedBy AS [Approved By]
FROM EmployeeApproval
ORDER BY EENum;

代码说明 #

1. 锚点成员：先定位所有拥有审批权限的员工，他们的审批人直接指向自身（或对应具备审批权的上级），作为递归的起始节点。
2. 递归成员：将无审批权限的员工通过Reports to字段关联到已确定审批人的上级，继承该上级的审批人信息，这个过程会自动遍历整个汇报链，直到所有员工都匹配到对应的审批人。
3. 最终查询：从递归结果中提取目标字段并排序，得到符合要求的输出。

该方案适配多层级汇报结构，无需临时表，完全通过CTE实现追溯逻辑。

内容的提问来源于stack exchange，提问作者Grant Baugh