不使用循环迭代查询用户所有上级的SQL实现问题排查

错误原因 #

1. 递归成员的关联对象错误：你在递归部分关联的是Hierarchy物理表，而非上一轮迭代生成的HierarchyData递归结果集，无法实现层级向上迭代
2. 关联条件逻辑错误：没有用上一轮返回的上级ID作为本次查询的员工ID，导致迭代无法逐层向上传递

正确实现代码 #

-- 表结构和测试数据和你原有代码一致，这里仅修改递归查询部分
WITH HierarchyData AS  
(  
    -- 锚点成员：查询起始用户的直接上级记录
    SELECT mbh.EmployeeId, mbh.SupervisorId 
    FROM Hierarchy mbh 
    WHERE mbh.EmployeeId = 4 -- 此处可替换为入参，比如查询C的上级就传入3
    UNION ALL  
    -- 递归成员：用上一轮查询到的上级ID作为本次查询的员工ID，迭代获取更上层上级
    SELECT mbh.EmployeeId, mbh.SupervisorId 
    FROM Hierarchy mbh  
    INNER JOIN HierarchyData hd ON mbh.EmployeeId = hd.SupervisorId
)

-- 查询1：如果需要展示上下级对应关系，使用该语句
SELECT 
    e.Name AS EmpName, 
    s.Name AS SupervisorName 
FROM HierarchyData h
JOIN Users e ON h.EmployeeId = e.Id
JOIN Users s ON h.SupervisorId = s.Id

-- 查询2：如果只需要返回所有上级的名字列表，使用该语句
-- SELECT s.Name AS SupervisorName 
-- FROM HierarchyData h
-- JOIN Users s ON h.SupervisorId = s.Id

执行效果 #

• 当入参为4（用户D）时，返回三组对应关系：D->C、C->B、B->A，上级列表为C、B、A
• 当入参为3（用户C）时，返回两组对应关系：C->B、B->A，上级列表为B、A

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