如何用SQL递归CTE查询用户部门编码 无编码则向上取上级编码

实现逻辑 #

递归CTE分为锚点和递归两部分：锚点加载所有员工原始数据，递归部分针对自身无有效编码的员工逐层向上遍历汇报链，直到拿到第一个非空部门编码为止，最后取每个员工的第一条有效结果即可。

兼容多数据库的SQL实现 #

WITH RECURSIVE EmpCodeHierarchy AS (
    -- 锚点：加载所有员工原始数据，记录当前待查询的上级、遍历层级
    SELECT 
        manager,
        emp,
        code,
        manager AS current_search_manager,
        1 AS level
    FROM Users
    
    UNION ALL
    
    -- 递归：仅处理未拿到有效编码的员工，向上关联上级信息
    SELECT 
        e.manager,
        e.emp,
        COALESCE(e.code, u.code) AS code,
        u.manager AS current_search_manager,
        e.level + 1 AS level
    FROM EmpCodeHierarchy e
    INNER JOIN Users u ON e.current_search_manager = u.emp
    WHERE e.code IS NULL -- 已拿到有效编码的员工终止递归
),
-- 给每个员工的递归结果按遍历顺序排序
RankedResults AS (
    SELECT 
        manager,
        emp,
        code,
        ROW_NUMBER() OVER (PARTITION BY emp ORDER BY level ASC) AS rn
    FROM EmpCodeHierarchy
)
-- 取每个员工第一个有效编码的结果
SELECT manager, emp, code
FROM RankedResults
WHERE rn = 1;

注意事项 #

你提供的INSERT测试数据和示例初始数据不一致，将插入语句中对应行调整为如下内容即可完全匹配预期输出：

('boss', 'subboss', 'AAA'),
('boss', 'subsub', 'SUBCODE'),

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