Oracle SQL多列树形层级（经理-员工）查询优化方案咨询

现有employees表，结构包含EMPLOYEE_ID、FIRST_NAME等字段，具体数据如下：

[原员工表格内容]

需要生成层级化输出，每列代表左侧父列对应的子级员工，期望输出结果如下：

[期望结果表格内容]

目前已尝试用多表连接实现该需求，想咨询是否有更优方案。以下是我尝试的递归CTE代码：

with org_chart (
employee_id, first_name, last_name, manager_id, lvl)
as (
select employee_id, first_name, last_name, manager_id, 1 lvl
from   employees
where  manager_id is null
union  all
select  e.employee_id, e.first_name, e.last_name,  e.manager_id, oc.lvl + 1
from   org_chart oc
join   employees e
on     e.manager_id = oc.employee_id
 )
select distinct o.manager_id, o.employee_id, o.lvl, e.employee_id as emp2
from org_chart o
left join employees e
on o.employee_id = e.manager_id
where o.manager_id is not null
order by o.manager_id;

更优实现思路 #

1. 优化现有递归CTE #

你当前的递归CTE是处理层级数据的标准方案，但可以做几点优化提升性能和灵活性：

• 移除DISTINCT：递归CTE本身不会生成重复数据（除非原表存在重复的manager_id关联），检查原表数据后可直接去掉，减少不必要的去重开销。
• 添加层级路径字段：在递归过程中维护一个path字段（比如用字符串拼接员工ID），方便后续快速筛选某一分支的员工，或用于格式化层级输出。
• 精简返回字段：只保留业务需要的字段，避免冗余数据的传输和计算。

优化后的示例代码：

with org_chart (
employee_id, first_name, manager_id, lvl, path)
as (
select employee_id, first_name, manager_id, 1 lvl, cast(employee_id as varchar(100)) as path
from   employees
where  manager_id is null
union  all
select  e.employee_id, e.first_name, e.manager_id, oc.lvl + 1, oc.path || ',' || e.employee_id
from   org_chart oc
join   employees e
on     e.manager_id = oc.employee_id
 )
-- 按层级分组生成多列输出（示例为固定3级，可根据实际层级调整）
select 
    max(case when lvl = 1 then employee_id end) as level1_emp,
    max(case when lvl = 2 then employee_id end) as level2_emp,
    max(case when lvl = 3 then employee_id end) as level3_emp
from org_chart
group by path
order by path;

2. 利用数据库原生层级函数 #

不同数据库提供了专门的层级查询语法，性能通常比通用递归CTE更优：

• Oracle：使用CONNECT BY原生语法

SELECT 
    employee_id, first_name, manager_id,
    LEVEL as lvl,
    SYS_CONNECT_BY_PATH(employee_id, '/') as path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

• PostgreSQL：14+版本支持CONNECT BY，也可借助ltree扩展实现高效层级管理
• SQL Server：使用hierarchyid类型或FOR PATH语法简化层级路径生成

3. 按需生成多列层级输出 #

如果你的目标是每列对应一个层级（比如Level1、Level2列），可根据层级固定程度选择两种方式：

• 条件聚合：适合层级数量固定的场景（比如最多3级），通过CASE WHEN分组生成对应列，性能优于多次自连接。
• 多次自连接：适合层级不固定但数量较少的场景，每一层级对应一次表连接，但层级过多时会导致性能下降。

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