sql - Oracle:层次查询连接依据

我编写了一个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 SuccessC D 10-OCT-19 SuccessB C 30-OCT-19 Success之后，我们对该数据集应用了分层查询，然后查找谁是雇员ID为“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, levelFROM emp_manager_mappingWHERE 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” 谁能帮助您找到预期的结果？ 最佳答案 查找层次结构中的所有行，然后按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 manageridFROM emp_manager_mapping eWHERE status = 'Success'START WITH empid = 'A'CONNECT BY NOCYCLE PRIOR managerid = empidORDER BY AppliedOn DESCFETCH FIRST ROW ONLY; 输出:TOP_MANAGERID |深度| EMPID |管理者:------------ | ----:| :---- | :--------C | 2 | A |乙db fiddle here关于sql - Oracle:层次查询连接依据，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59415657/