Oracle历史表递归查询

Question

我想在用户历史表中做一个递归查询。我尝试了下面的查询，它只给出一个记录。因为这是一个历史表，所以对于特定的用户，会有多条记录，其中包含有效日期。

WITH superVis(EMPLID,CH_SUPV_ID) AS ( 
SELECT A.EMPLID,A.CH_SUPV_ID
FROM PSOFTUDB.PS_CH_RPTS_TO_TBL A WHERE A.EMPLID = @Param AND A.EFFDT = (select MAX(A2.EFFDT) from PSOFTUDB.PS_CH_RPTS_TO_TBL A2 WHERE A2.EMPLID = A.EMPLID)
UNION ALL 
SELECT e.EMPLID,e.CH_SUPV_ID FROM 
PSOFTUDB.PS_CH_RPTS_TO_TBL e,superVis o where 
o.CH_SUPV_ID = e.EMPLID 
) select * from superVis;

我想要用户的层次与受人尊敬的经理。我能够在DB2中实现这一点。当更改为oracle时，我得到错误。

当前管理器的最新生效日期是查询中要使用的数据。

样本数据:

EMPLID  CH_SUPV_ID EFFDT
1844854 1730352 2020-12-03 00:00:00
1730352 1461958 2018-12-17 00:00:00
1461958 246001  2014-02-04 00:00:00
1461958 954507  2021-06-10 00:00:00
246001  123345  1999-04-12 00:00:00
246001  17139   1999-08-23 00:00:00
246001  18382   2004-04-13 00:00:00
246001  18442   2007-07-27 00:00:00
954507  971510  2019-05-01 00:00:00

预期输出:

EMLID   CH_SUPV_ID EFFDAT
1844854 1730352 2020-12-03 00:00:00
1730352 1461958 2018-12-17 00:00:00
1461958 954507  2021-06-10 00:00:00
954507  971510  2019-05-01 00:00:00

Answer 1

首先，处理生效日期。这是我在"temp"CTE。它显示了每个字段的最大生效日期。

这样做了，你可以很容易地得到你需要使用oracle的分层查询功能。

with temp as (
select EMPLID, CH_SUPV_ID, EFFDT, max(effdt) over(partition by emplid) maxdate
from PS_CH_RPTS_TO_TBL
)
select * 
from temp
connect by nocycle EMPLID = prior CH_SUPV_ID and effdt = maxdate
start with emplid = 1844854