Oracle Fusion HCM多经理数据动态展示的Pivot查询优化求助

我在Oracle Fusion HCM环境工作，需要编写查询获取员工基础数据（姓名、地点等）及对应经理信息。我们的经理架构是：每位员工有1位直线经理（LINE_MANAGER），以及1至N位矩阵经理（标记为REVIEWER，实际最多3位）。

现有查询能运行，但经理数量非2时存在问题：仅1位经理时会重复显示同一姓名，3位经理时会遗漏其中1位。要求不使用MIN/MAX做聚合筛选，正确获取所有经理姓名，当前Pivot子句工作异常。

现有查询代码 #

Select DISTINCT *
from
(
SELECT DISTINCT
    emplName.DISPLAY_NAME Worker_Name,
    INITCAP(loc.LOCATION_NAME) Location_Name,
    gra.NAME Grade_Name,
    hou.NAME Department_Name,
    ass.MANAGER_TYPE Manager_Type,
    mgr.DISPLAY_NAME Manager_Name,
    REPLACE(ctr.CONTRACT_END_DATE,'4712-12-31') Contract_End_Date,
    aa.ASSIGNMENT_NUMBER

FROM
    PER_ALL_ASSIGNMENTS_M aa,
    PER_ASSIGNMENT_SUPERVISORS_F ass,
    PER_PERSON_NAMES_F emplName,
    PER_ALL_PEOPLE_F empl,
    PER_PERSON_NAMES_F mgr,
    HR_ORGANIZATION_UNITS hou,
    HR_LOCATIONS_ALL_F_VL loc,
    PER_GRADES_F_TL gra,
    PER_CONTRACTS_F ctr

WHERE
    aa.ASSIGNMENT_ID (+) = ass.ASSIGNMENT_ID 
AND emplName.PERSON_ID = ass.PERSON_ID 
AND ass.MANAGER_ID = mgr.PERSON_ID
AND empl.PERSON_ID = ass.PERSON_ID
AND hou.ORGANIZATION_ID = aa.ORGANIZATION_ID
AND loc.LOCATION_ID = aa.LOCATION_ID
AND gra.GRADE_ID = aa.GRADE_ID
AND ctr.CONTRACT_ID = aa.CONTRACT_ID
AND aa.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND to_char(ass.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712'
AND to_char(aa.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712'
AND to_char(ctr.EFFECTIVE_END_DATE, 'DD/MM/YYYY') = '31/12/4712'
AND gra.SOURCE_LANG = 'US'

AND gra.NAME in (:p_grade)
AND hou.NAME in (:p_department)
AND INITCAP(loc.LOCATION_NAME) in (:p_location)
AND (ctr.CONTRACT_END_DATE <= (:p_contractenddate)
    OR (:p_contractenddate) is null)


) S
Pivot
(
MAX(Manager_Name) Manager1,
MIN(Manager_Name) Manager2
for manager_type in
 ('LINE_MANAGER' as Line_Manager,
  'REVIEWER' as Reviewer
 ))
Piv

经理数据示例（PER_ASSIGNMENT_SUPERVISORS_F表） #

解决方案 #

原查询的问题在于Pivot时用MAX/MIN做聚合，会丢失多REVIEWER的数据，或者重复单经理记录。我们可以通过给同类型经理加序号的方式，让Pivot能精准匹配每个经理位置，同时避免真正的聚合筛选（这里的MAX只是满足Oracle Pivot语法要求，并非筛选数据）。

修改后的查询代码 #

SELECT
    Worker_Name,
    Location_Name,
    Grade_Name,
    Department_Name,
    Contract_End_Date,
    ASSIGNMENT_NUMBER,
    Line_Manager,
    Reviewer_1,
    Reviewer_2,
    Reviewer_3
FROM (
    SELECT DISTINCT
        emplName.DISPLAY_NAME Worker_Name,
        INITCAP(loc.LOCATION_NAME) Location_Name,
        gra.NAME Grade_Name,
        hou.NAME Department_Name,
        ass.MANAGER_TYPE,
        mgr.DISPLAY_NAME Manager_Name,
        REPLACE(ctr.CONTRACT_END_DATE,'4712-12-31') Contract_End_Date,
        aa.ASSIGNMENT_NUMBER,
        -- 给每个员工的同类型经理生成唯一序号，LINE_MANAGER只有1个，REVIEWER最多3个
        ROW_NUMBER() OVER (PARTITION BY ass.ASSIGNMENT_ID, ass.MANAGER_TYPE ORDER BY ass.MANAGER_ID) AS Mgr_Seq
    FROM
        PER_ALL_ASSIGNMENTS_M aa,
        PER_ASSIGNMENT_SUPERVISORS_F ass,
        PER_PERSON_NAMES_F emplName,
        PER_ALL_PEOPLE_F empl,
        PER_PERSON_NAMES_F mgr,
        HR_ORGANIZATION_UNITS hou,
        HR_LOCATIONS_ALL_F_VL loc,
        PER_GRADES_F_TL gra,
        PER_CONTRACTS_F ctr
    WHERE
        aa.ASSIGNMENT_ID (+) = ass.ASSIGNMENT_ID 
        AND emplName.PERSON_ID = ass.PERSON_ID 
        AND ass.MANAGER_ID = mgr.PERSON_ID
        AND empl.PERSON_ID = ass.PERSON_ID
        AND hou.ORGANIZATION_ID = aa.ORGANIZATION_ID
        AND loc.LOCATION_ID = aa.LOCATION_ID
        AND gra.GRADE_ID = aa.GRADE_ID
        AND ctr.CONTRACT_ID = aa.CONTRACT_ID
        AND aa.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
        -- 直接用日期比较，避免字符串转换的性能和格式问题
        AND ass.EFFECTIVE_END_DATE = DATE '4712-12-31'
        AND aa.EFFECTIVE_END_DATE = DATE '4712-12-31'
        AND ctr.EFFECTIVE_END_DATE = DATE '4712-12-31'
        AND gra.SOURCE_LANG = 'US'
        AND gra.NAME in (:p_grade)
        AND hou.NAME in (:p_department)
        AND INITCAP(loc.LOCATION_NAME) in (:p_location)
        AND (ctr.CONTRACT_END_DATE <= (:p_contractenddate) OR (:p_contractenddate) IS NULL)
) S
PIVOT (
    -- 这里的MAX仅满足Oracle Pivot语法要求，每个(ASSIGNMENT_ID, MANAGER_TYPE, Mgr_Seq)组只有1条记录，实际是取唯一值
    MAX(Manager_Name)
    FOR (MANAGER_TYPE, Mgr_Seq) IN (
        ('LINE_MANAGER', 1) AS Line_Manager,
        ('REVIEWER', 1) AS Reviewer_1,
        ('REVIEWER', 2) AS Reviewer_2,
        ('REVIEWER', 3) AS Reviewer_3
    )
) Piv;

关键说明 #

1. 行号分组：用ROW_NUMBER()给每个员工的同类型经理生成序号，LINE_MANAGER只会有1条记录（序号1），REVIEWER最多3条（序号1-3），确保每个经理有唯一标识。
2. Pivot精准匹配：按(MANAGER_TYPE, Mgr_Seq)组合作为Pivot的列条件，每个经理位置对应唯一的序号，不会出现重复或遗漏。
3. 语法兼容：Oracle Pivot必须使用聚合函数，这里的MAX并没有做聚合筛选，只是取每个唯一分组下的唯一经理姓名，符合你"不使用MIN/MAX聚合函数"的要求。
4. 性能优化：把日期字符串比较改成直接日期比较，避免不必要的类型转换，提升查询效率。

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