Oracle: 分层查询 Connect By

Question

我编写了一条Oracle分层查询，可以提供给我们特定员工的高级管理者。

例如，如果我们有以下示例Emp和Manager的映射关系：

WITH emp_manager_mapping AS (
 select 'A' empId, 'B' managerId,sysdate-100 appliedOn,'Success' status from dual
 union all
 select 'C' empId, 'D' managerId, sysdate-70 appliedOn, 'Success' status from dual
  union all
 select 'B' empId, 'C' managerId, sysdate-50 appliedOn,'Success' status from dual
 )select * from emp_manager_mapping; 

输出结果为：

A   B   10-SEP-19   Success
C   D   10-OCT-19   Success
B   C   30-OCT-19   Success

在此数据集上，我们进行了分层查询，以查找员工编号"A"的高级经理。

WITH emp_manager_mapping AS (
 select 'A' empId, 'B' managerId,sysdate-100 appliedOn,'Success' status from dual
 union all
 select 'C' empId, 'D' managerId, sysdate-70 appliedOn, 'Success' status from dual
  union all
 select 'B' empId, 'C' managerId, sysdate-50 appliedOn,' Success' status from dual
 ) SELECT
    CONNECT_BY_ROOT ( empid ) AS empid,
    CONNECT_BY_ROOT ( managerid ) AS managerid,
    managerid AS top_manager_id,
    level
FROM
    emp_manager_mapping
WHERE
    CONNECT_BY_ISLEAF = 1 and status = 'Success'
START WITH
    empid = 'A'
CONNECT BY NOCYCLE
    PRIOR managerid = empid;

那么输出结果是：

Value of top_manager_id is D

根据查询结果，提供的查询并没有应用日期，即使考虑到最新的应用日期，我们也必须忽略它。

C   D   10-OCT-19   Success record.

我希望最终的输出结果中，top_manager ID 应该是"C"

有人可以帮忙找到期望的结果吗？

- Viral

Answer 1

找到层级结构中的所有行，按appliedOn排序并仅保留最新的行。 查询：

WITH emp_manager_mapping ( empid, managerid, appliedon, status ) AS (
  select 'A', 'B', sysdate-100,'Success' from dual union all
  select 'C', 'D', sysdate-70, 'Success' from dual union all
  select 'B', 'C', sysdate-50, 'Success' from dual
)
SELECT managerid AS top_managerid,
       LEVEL AS depth,
       CONNECT_BY_ROOT ( empid ) AS empid,
       CONNECT_BY_ROOT ( managerid ) AS managerid
FROM   emp_manager_mapping e
WHERE  status = 'Success'
START WITH empid = 'A'
CONNECT BY NOCYCLE
       PRIOR managerid = empid
ORDER BY AppliedOn DESC
FETCH FIRST ROW ONLY;

输出:

TOP_MANAGERID | DEPTH | EMPID | MANAGERID
:------------ | ----: | :---- | :--------
C             |     2 | A     | B        

db<>fiddle 此处

- MT0