PostgreSQL递归查询实现员工最高层级经理获取求助

你的原查询仅针对单个员工（ID=9）追溯顶层经理，无法覆盖所有员工的需求。以下是实现所有员工获取其层级最高经理的递归SQL方案：

WITH RECURSIVE top_manager_search AS (
    -- 锚点成员：初始化所有员工的查询，携带自身信息与当前追溯节点（初始为员工自己）
    SELECT
        e.id AS emp_id,
        e.nom AS emp_nom,
        e AS current_node
    FROM employe e
    UNION ALL
    -- 递归成员：向上追溯当前节点的经理，直到节点无上级（manager为空）
    SELECT
        tms.emp_id,
        tms.emp_nom,
        e AS current_node
    FROM top_manager_search tms
    JOIN employe e ON tms.current_node.manager = e.id
    WHERE tms.current_node.manager IS NOT NULL
)
-- 筛选每个员工的顶层经理记录，并格式化输出
SELECT
    emp_id AS ID,
    emp_nom AS NOM,
    -- 若员工自身是顶层经理（无上级），则HIGHEST_MANAGER为空，否则取顶层经理ID
    CASE WHEN emp_id = current_node.id THEN NULL ELSE current_node.id END AS HIGHEST_MANAGER
FROM (
    SELECT
        emp_id,
        emp_nom,
        current_node,
        -- 为每个员工的递归记录排序，顶层节点的记录会被优先选中
        ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY (current_node.manager IS NULL) DESC) AS rn
    FROM top_manager_search
) sub
WHERE rn = 1
ORDER BY ID;

逻辑说明： #

1. 递归CTE（top_manager_search）：
   • 锚点部分：为每个员工创建初始记录，包含员工自身ID、姓名，以及当前追溯的节点（即员工自己）。
   • 递归部分：如果当前节点存在上级经理，就继续向上关联查询经理节点，直到节点的manager字段为空（到达顶层）。
2. 结果筛选：
   • 通过ROW_NUMBER()窗口函数为每个员工的递归记录分组排序，确保顶层节点的记录（current_node.manager IS NULL为真）被选中。
   • 最后通过CASE语句处理顶层员工的情况（自身无上级时，HIGHEST_MANAGER为空）。

执行该查询后，即可得到你期望的所有员工及其最高经理的结果。

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