如何在Snowflake中用递归连接实现列化经理层级查询

原需求是替代多次LEFT JOIN的层级查询，用Snowflake递归CTE实现每个员工的各级经理信息单独列示（而非行式层级结果），且无需指定单个员工。

原多次LEFT JOIN查询代码 #

select
    d1.employee_id,
    d1.employee_id1,
    d1.employee_id2,
    d1.employee_id3,
    d2.employee_id1 as employee_id4,
    d2.manager_name as employee_id4_name,
    d3.employee_id1 as employee_id5,
    d3.manager_name as employee_id5_name,
    d4.employee_id1 as employee_id6,
    d4.manager_name as employee_id6_name,
    d5.employee_id1 as employee_id7,
    d5.manager_name as employee_id7_name
from employees d1
left join employees d2
on d1.employee_id3 = d2.employee_id
and d1.report_effective_date = d2.report_effective_date
left join employees d3
on d2.employee_id1 = d3.employee_id
and d2.report_effective_date = d3.report_effective_date
left join employees d4
on d3.employee_id1 = d4.employee_id
and d3.report_effective_date = d4.report_effective_date
left join employees d5
on d4.employee_id1 = d5.employee_id
and d4.report_effective_date = d5.report_effective_date

尝试的递归CTE代码（行式结果，需指定员工） #

with cte as (
  select
    employee_id,
    employee_name,
    employee_id1,
    manager_name
  from employees
  where employee_name = <name>
  
  union all

  select
    employee.employee_id,
    employee.employee_name,
    employee.employee_id1,
    employee.manager_name
  from employees
  join cte
  on employee.employee_id = cte.employee_id1
)
select * from cte

解决方案：递归CTE + 行转列 #

通过递归CTE遍历所有员工的层级路径，再用聚合函数转置为列，实现所有员工的各级经理信息单独列示：

WITH employee_hierarchy AS (
    -- 锚点成员：所有员工，初始化直接经理为第1层级
    SELECT
        employee_id AS emp_id,
        employee_name AS emp_name,
        employee_id1 AS manager_id,
        manager_name AS manager_name,
        report_effective_date,
        1 AS level_num
    FROM employees
    UNION ALL
    -- 递归成员：向上遍历经理的上级，层级数递增
    SELECT
        eh.emp_id,
        eh.emp_name,
        e.employee_id1 AS manager_id,
        e.manager_name AS manager_name,
        eh.report_effective_date,
        eh.level_num + 1 AS level_num
    FROM employee_hierarchy eh
    JOIN employees e 
        ON eh.manager_id = e.employee_id
        AND eh.report_effective_date = e.report_effective_date
)
-- 转置层级记录为单独列
SELECT
    emp_id AS employee_id,
    emp_name AS employee_name,
    report_effective_date,
    MAX(CASE WHEN level_num = 1 THEN manager_id END) AS manager_1_id,
    MAX(CASE WHEN level_num = 1 THEN manager_name END) AS manager_1_name,
    MAX(CASE WHEN level_num = 2 THEN manager_id END) AS manager_2_id,
    MAX(CASE WHEN level_num = 2 THEN manager_name END) AS manager_2_name,
    MAX(CASE WHEN level_num = 3 THEN manager_id END) AS manager_3_id,
    MAX(CASE WHEN level_num = 3 THEN manager_name END) AS manager_3_name,
    MAX(CASE WHEN level_num = 4 THEN manager_id END) AS manager_4_id,
    MAX(CASE WHEN level_num = 4 THEN manager_name END) AS manager_4_name,
    MAX(CASE WHEN level_num = 5 THEN manager_id END) AS manager_5_id,
    MAX(CASE WHEN level_num = 5 THEN manager_name END) AS manager_5_name
FROM employee_hierarchy
GROUP BY emp_id, emp_name, report_effective_date
ORDER BY emp_id;

说明 #

1. 递归CTE employee_hierarchy 会为每个员工生成所有层级的经理记录，level_num 标记当前经理的层级（1为直接上级，2为上上级，以此类推）
2. 用CASE结合MAX聚合函数，将不同层级的经理ID和姓名转成单独列，替代原多次LEFT JOIN的繁琐写法
3. 无需指定单个员工，查询会返回所有员工的完整层级经理列表
4. 若层级超过5级，只需继续添加对应层级的CASE语句即可

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