Snowflake层级查询问题：无法获取员工对应经理名称

现有员工表数据如下：

TITLE                           EMPLOYEE_ID   MANAGER_ID
President                         1 
Vice President Engineering        10             1
Programmer                        100            10
QA Engineer                       101            10
Vice President HR                 20             1
Health Insurance Analyst          200            20

尝试使用以下层级查询获取结果：

select employee_id, manager_id, title,  prior report_title
  from employees
    start with title = 'President'
    connect by
      manager_id = prior employee_id
  order by employee_id;

但返回结果不符合预期，预期结果示例（需覆盖所有员工对应经理关系）：

EMPLOYEE_ID   MANAGER_ID    title                         report_title
  10             1          Vice President Engineering    President 

错误分析 #

你的SQL存在两个核心问题：

1. prior report_title中的report_title并非表中存在的字段，应该用prior title来引用上级（经理）的职位名称；
2. 当前查询逻辑本身没问题，但如果要完整获取所有员工的经理信息，需要确保遍历覆盖所有子节点。

修正后的SQL #

方案1：使用层级查询（保留树形遍历逻辑） #

select 
  employee_id, 
  manager_id, 
  title, 
  prior title as report_title
from employees
start with title = 'President'
connect by manager_id = prior employee_id
order by employee_id;

方案2：使用自连接（更直观的关联方式） #

如果不需要树形遍历的额外特性，自连接写法更易懂：

select 
  e.employee_id, 
  e.manager_id, 
  e.title, 
  m.title as report_title
from employees e
left join employees m on e.manager_id = m.employee_id
order by e.employee_id;

执行结果 #

方案1和方案2都会返回完整的员工-经理对应关系：

EMPLOYEE_ID   MANAGER_ID    title                         report_title
1             null          President                     null
10            1             Vice President Engineering    President
20            1             Vice President HR             President
100           10            Programmer                    Vice President Engineering
101           10            QA Engineer                   Vice President Engineering
200           20            Health Insurance Analyst      Vice President HR

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