如何无需游标循环实现基于组织单元的员工主管ID查询？

表结构 #

表A：Employee #

表B：OrgUnit #

查询逻辑要求 #

1. 从Employee表获取指定EmpID（如1001）对应的OrgUnit
2. 以该OrgUnit为条件，在OrgUnit表中查找**Code='B'且Status='012'**的记录，其SID即为目标主管ID
3. 若步骤2无结果，则查找该OrgUnit下**Code='A'且Status='002'**的记录，将其SID作为新的OrgUnit
4. 重复步骤2-3：用新的OrgUnit查找Code='B'+Status='012'，找不到则继续找Code='A'+Status='002'的记录，直到找到符合条件的SID或遍历完层级
5. 额外要求：找到Code='B'+Status='012'的记录后，需以其SID作为OrgUnit查找Code='B'+Status='008'的SID

解决方案：递归CTE实现层级查询 #

递归CTE可高效处理层级遍历需求，无需游标循环。以下是适配SQL Server的实现（MySQL 8.0+、PostgreSQL等支持递归CTE的数据库可调整语法复用）：

WITH RecursiveOrg AS (
    -- 锚点：获取员工对应的初始组织单元
    SELECT 
        e.OrgUnit AS CurrentOrg,
        0 AS Level
    FROM Employee e
    WHERE e.EmpID = 1001
        -- 过滤生效期间的员工记录（可选，按需保留）
        AND CONVERT(DATE, e.BeginDate, 112) <= GETDATE() 
        AND CONVERT(DATE, e.EndDate, 112) >= GETDATE()

    UNION ALL

    -- 递归：逐层查找符合条件的组织记录
    SELECT 
        o.SID AS CurrentOrg,
        ro.Level + 1
    FROM RecursiveOrg ro
    JOIN OrgUnit o 
        ON o.OrgUnit = ro.CurrentOrg
        -- 过滤生效期间的组织记录（可选，按需保留）
        AND CONVERT(DATE, o.BeginDate, 112) <= GETDATE() 
        AND CONVERT(DATE, o.EndDate, 112) >= GETDATE()
    WHERE 
        -- 优先查找目标记录，不存在则走A002分支继续递归
        NOT EXISTS (
            SELECT 1 FROM OrgUnit o2
            WHERE o2.OrgUnit = ro.CurrentOrg
            AND o2.Code = 'B' AND o2.Status = '012'
            AND CONVERT(DATE, o2.BeginDate, 112) <= GETDATE() 
            AND CONVERT(DATE, o2.EndDate, 112) >= GETDATE()
        )
        AND o.Code = 'A' AND o.Status = '002'
)
-- 最终查询：获取主管ID及对应008状态的上级SID
SELECT 
    supervisor.SID AS SupervisorID,
    upper_level.SID AS UpperLevelSID
FROM (
    -- 获取层级中第一个匹配B012的记录
    SELECT TOP 1 o.SID
    FROM RecursiveOrg ro
    JOIN OrgUnit o 
        ON o.OrgUnit = ro.CurrentOrg
        AND o.Code = 'B' AND o.Status = '012'
        AND CONVERT(DATE, o.BeginDate, 112) <= GETDATE() 
        AND CONVERT(DATE, o.EndDate, 112) >= GETDATE()
    ORDER BY ro.Level ASC
) supervisor
-- 关联查找B008状态的上级记录
LEFT JOIN OrgUnit upper_level 
    ON upper_level.OrgUnit = supervisor.SID
    AND upper_level.Code = 'B' AND upper_level.Status = '008'
    AND CONVERT(DATE, upper_level.BeginDate, 112) <= GETDATE() 
    AND CONVERT(DATE, upper_level.EndDate, 112) >= GETDATE();

方案说明 #

1. 锚点初始化：先定位员工对应的初始组织单元，作为遍历起点
2. 递归遍历逻辑：每次递归优先检查当前组织是否存在B012记录，不存在则通过A002记录跳转至下一层组织，自动实现循环查找
3. 终止条件：找到B012记录时，递归自动停止（NOT EXISTS条件不再满足）
4. 日期过滤：加入生效期间判断，确保只查询有效的组织关系（业务不需要可直接删除相关条件）
5. 步骤7处理：找到主管ID后，通过左关联获取对应的B008上级SID

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