SQL查询获取离职员工最新直属主管及职位的问题求助

我需要编写SQL查询离职员工的最新直属主管及其职位，但当前关联多表的查询返回了该员工的多条主管及职位结果，期望仅返回一位最新主管及对应职位。

已知针对单表PER_ALL_ASSIGNMENTS_F按EFFECTIVE_START_DATE降序排序后，首行即可得到目标员工正确的SUPERVISOR_ID和JOB_ID：

SELECT SUPERVISOR_ID,JOB_ID 
FROM PER_ALL_ASSIGNMENTS_F 
WHERE PERSON_ID = 24387 
ORDER BY EFFECTIVE_START_DATE DESC;

但以下关联多表的查询返回了多条结果，需要调整以实现需求：

select 
DISTINCT
PO.SEGMENT1,
HR.NAME,
PAPF.FULL_NAME AS LEAVER_FULL_NAME,
PAPF.PERSON_ID LEAVER_ID,
PAAF.SUPERVISOR_ID,
PAPF2.FULL_NAME AS SUPERVISOR,
PJ.Name JOB_NAME
FROM po_headers_all PO,
HR_OPERATING_UNITS HR,
po_distributions_all PD,
PER_ALL_PEOPLE_F PAPF,
PER_ALL_PEOPLE_F PAPF2,
PER_ALL_ASSIGNMENTS_F PAAF,
per_person_types ppt ,
PER_JOBS PJ
WHERE PD.PO_HEADER_ID=PO.PO_HEADER_ID
AND PO.CLOSED_CODE = 'OPEN'
AND PO.TYPE_LOOKUP_CODE LIKE 'STANDARD'
AND PAPF.PERSON_ID= PD.DELIVER_TO_PERSON_ID
AND PAAF.PERSON_ID = PAPF.PERSON_ID
AND PAPF2.PERSON_ID =PAAF.SUPERVISOR_ID
AND HR.ORGANIZATION_ID = PO.ORG_ID
AND PJ.JOB_ID=PAAF.JOB_ID
AND PJ.NAME NOT IN ('Director','Vice President',' CEO','CFO','Executive VP','GlobalOperations EVP','Mfg VP','Sourcing and Proc VP','Sr. Vice President')
AND PJ.NAME NOT LIKE 'Senior VP%GM'
--AND   PAPF.EFFECTIVE_START_DATE BETWEEN :P_DATE_FROM AND P_DATE_TO
--AND   PAAF.EFFECTIVE_START_DATE BETWEEN :P_DATE_FROM AND P_DATE_TO
--AND PAPF.EFFECTIVE_START_DATE BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD')
--AND PAAF.EFFECTIVE_START_DATE BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND TO_DATE('2023-12-31', 'YYYY-MM-DD') 
AND   papf.person_type_id           = ppt.person_type_id
AND   UPPER(PPT.SYSTEM_PERSON_TYPE) = UPPER('EX_EMP')
 and PO.SEGMENT1 in ('2044611')
ORDER BY PAPF2.FULL_NAME

解决方案 #

核心问题是PER_ALL_ASSIGNMENTS_F表中每个员工可能存在多条历史分配记录，直接关联会带出所有主管信息。我们可以用**窗口函数ROW_NUMBER()**筛选出每个员工最新的分配记录，再参与关联查询：

修改后的完整SQL #

SELECT 
PO.SEGMENT1,
HR.NAME,
PAPF.FULL_NAME AS LEAVER_FULL_NAME,
PAPF.PERSON_ID LEAVER_ID,
PAAF_LATEST.SUPERVISOR_ID,
PAPF2.FULL_NAME AS SUPERVISOR,
PJ.Name JOB_NAME
FROM po_headers_all PO
JOIN HR_OPERATING_UNITS HR ON HR.ORGANIZATION_ID = PO.ORG_ID
JOIN po_distributions_all PD ON PD.PO_HEADER_ID=PO.PO_HEADER_ID
JOIN PER_ALL_PEOPLE_F PAPF ON PAPF.PERSON_ID= PD.DELIVER_TO_PERSON_ID
JOIN per_person_types ppt ON papf.person_type_id = ppt.person_type_id
-- 用子查询获取每个员工最新的分配记录
JOIN (
    SELECT 
        PERSON_ID,
        SUPERVISOR_ID,
        JOB_ID,
        ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY EFFECTIVE_START_DATE DESC) AS RN
    FROM PER_ALL_ASSIGNMENTS_F
) PAAF_LATEST ON PAAF_LATEST.PERSON_ID = PAPF.PERSON_ID AND PAAF_LATEST.RN = 1
JOIN PER_ALL_PEOPLE_F PAPF2 ON PAPF2.PERSON_ID = PAAF_LATEST.SUPERVISOR_ID
JOIN PER_JOBS PJ ON PJ.JOB_ID=PAAF_LATEST.JOB_ID
WHERE 
PO.CLOSED_CODE = 'OPEN'
AND PO.TYPE_LOOKUP_CODE LIKE 'STANDARD'
AND PJ.NAME NOT IN ('Director','Vice President',' CEO','CFO','Executive VP','GlobalOperations EVP','Mfg VP','Sourcing and Proc VP','Sr. Vice President')
AND PJ.NAME NOT LIKE 'Senior VP%GM'
AND UPPER(PPT.SYSTEM_PERSON_TYPE) = UPPER('EX_EMP')
AND PO.SEGMENT1 IN ('2044611')
ORDER BY PAPF2.FULL_NAME

关键改动说明 #

1. 将原直接关联的PER_ALL_ASSIGNMENTS_F替换为带窗口函数的子查询，通过PARTITION BY PERSON_ID按员工分组，ORDER BY EFFECTIVE_START_DATE DESC排序后取行号RN=1的记录，确保每个员工只保留最新的分配信息
2. 替换了原查询中的隐式连接为显式JOIN，提升SQL可读性
3. 移除了原查询中的DISTINCT，因为窗口函数已经确保每个员工仅返回一条最新记录，无需去重

内容的提问来源于stack exchange，提问作者Nayan Semwal