时间戳

首页 > 解决方案 > 在 oracle 中通过 Prior 子句查找没有 Connect by Prior 子句的给定员工的上级经理

sql - 在 oracle 中通过 Prior 子句查找没有 Connect by Prior 子句的给定员工的上级经理

问题描述

我已经编写了查询以使用先验连接找到给定员工的高级经理，但我需要避免先验连接

源表：

Employee_Id Manager Id
1            10
10           20
20           Null
2             5
5             7
7            null
3            6
6           Null

输出表 

Input          Output
Employee_id    Employee_ID
1              20
2               7
3               6
5               7

我的方法：

select * from (
SELECT *
FROM EMPLOYEEs 
START WITH EMPLOYEE_ID = 103
CONNECT BY EMPLOYEE_ID = PRIOR MANAGER_ID
) where manager_id is null

替代方法：

with cte (EMPLOYEE_ID,MANAGER_ID,lev) as (
      select EMPLOYEE_ID, MANAGER_ID, 0 as lev
      from employees
      union all
      select cte.EMPLOYEE_ID, employees.MANAGER_ID, lev + 1
      from cte join
           employees
           on cte.MANAGER_ID = employees.EMPLOYEE_ID

     )
    select * from cte where employee_id=103 and MANAGER_ID is null;

但没有通过替代方法获得预期的输出。

标签： sqloracle

解决方案

在递归子查询分解中，您需要识别根 employee_id 并在最终查询中使用它，如下所示：

WITH your_table AS
 (SELECT 1 employee_id, 10 manager_id FROM dual UNION ALL
  SELECT 10 employee_id, 20 manager_id FROM dual UNION ALL
  SELECT 20 employee_id, NULL manager_id FROM dual UNION ALL
  SELECT 2 employee_id, 5 manager_id FROM dual UNION ALL
  SELECT 5 employee_id, 7 manager_id FROM dual UNION ALL
  SELECT 7 employee_id, NULL manager_id FROM dual UNION ALL
  SELECT 3 employee_id, 6 manager_id FROM dual UNION ALL
  SELECT 6 employee_id, NULL manager_id FROM dual),
recursive(employee_id,
          manager_id,
          root_emp_id) AS
 (SELECT employee_id,
         manager_id,
         employee_id root_emp_id
  FROM   your_table
  WHERE  manager_id IS NOT NULL
  UNION ALL
  SELECT yt.employee_id,
         yt.manager_id,
         r.root_emp_id
  FROM   recursive r
  INNER  JOIN your_table yt
  ON     r.manager_id = yt.employee_id)
SELECT root_emp_id employee_id,
       employee_id ultimate_manager_id
FROM   recursive
WHERE  manager_id IS NULL
ORDER BY employee_id;

EMPLOYEE_ID ULTIMATE_MANAGER_ID
----------- -------------------
          1                  20
          2                   7
          3                   6
          5                   7
         10                  20

这模仿了分层查询中的 connect_by_root 函数。

推荐阅读