PostgreSQL递归实现员工空值沿管理层级继承补全

核心规则 #

• 员工department、lob字段为空时，沿manager_id关联的管理链向上逐层查找对应字段的第一个非空值继承
• 字段取值优先级：当前员工自身非空值 > 上级管理链对应字段非空值 > 遍历至根节点仍为空则保留空值
• 两个字段独立判断继承逻辑，互不影响
• 最终返回全量员工补全字段后的单条结果，不返回层级遍历中间行

测试表结构与初始数据 #

create table employee(
employee_id int primary key,    
name text,
manager_id int,
department text,
lob text);

insert into employee
(employee_id,name,manager_id,department,lob)
values
(12,'a',null,'IT','BFI'),
(3,'b',12,'sales',null),
(4,'c',3,null,'Banking'),
(6,'d',12,null,null),
(7,'e',4,null,null),
(10,'f',7,null,null);

原有递归实现的问题 #

之前写的CTE存在两个核心错误：

• 锚点仅查询了employee_id=10的单个员工，且递归过程未做结果去重，会返回从当前员工到根节点的所有中间层级行，无法输出全量员工的单条补全结果
• coalesce参数顺序错误，且未做递归剪枝，无法实现「优先取自身值、为空才继承上级」的列级独立继承逻辑

正确实现代码 #

WITH RECURSIVE emp_inherit AS (
    -- 锚点：全量员工作为遍历起点，记录自身原始字段值、当前待追溯的上级ID、遍历深度
    SELECT
        employee_id,
        name,
        manager_id AS current_check_manager_id,
        department AS inherit_department,
        lob AS inherit_lob,
        1 AS depth
    FROM employee
    UNION ALL
    -- 递归向上追溯，仅为空的字段尝试取上级值填充
    SELECT
        ei.employee_id,
        ei.name,
        e.manager_id AS current_check_manager_id,
        COALESCE(ei.inherit_department, e.department) AS inherit_department,
        COALESCE(ei.inherit_lob, e.lob) AS inherit_lob,
        ei.depth + 1 AS depth
    FROM emp_inherit ei
    INNER JOIN employee e 
        ON ei.current_check_manager_id = e.employee_id
    -- 递归终止条件：两个字段都已补全，或已追溯到根节点无上级时停止
    WHERE (ei.inherit_department IS NULL OR ei.inherit_lob IS NULL)
      AND ei.current_check_manager_id IS NOT NULL
)
-- 按员工分组，取遍历深度最小的记录（自身值优先级最高，其次是最近上级的值）
SELECT DISTINCT ON (employee_id)
    employee_id,
    name,
    inherit_department AS department,
    inherit_lob AS lob
FROM emp_inherit
ORDER BY employee_id, depth;

实现逻辑说明 #

• 递归锚点覆盖所有员工，无需单独指定员工ID即可一次性计算全量员工的补全结果
• 两个字段独立通过COALESCE判断，只有当前字段为空时才会取上级的对应字段值，不会覆盖自身已有的非空值
• 增加递归剪枝条件：当两个字段都补全，或者已经追溯到根节点（无上级）时立刻终止当前员工的向上遍历，避免无效递归计算
• 利用PostgreSQL的DISTINCT ON语法，按员工ID分组后取遍历深度最小的记录，保证每个员工仅返回一条优先级最高的补全结果

结果验证 #

针对提供的测试数据，查询返回结果如下，完全符合预期规则：

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