Oracle SQL查询去重：保留最新薪资更新数据的技术咨询

问题分析 #

你的查询返回重复数据主要源于两个核心问题：

• 薪资表CMP_SALARY中同一员工存在多条薪资记录，原分组逻辑无法正确关联最新更新对应的薪资金额（原查询中TRUNC(SALARY_AMOUNT,2)未纳入聚合或分组规则，逻辑存在缺陷）；
• 关联表（如PER_PHONES、PER_EMAIL_ADDRESSES）可能存在同一员工的多条记录，导致表连接后数据被重复展开。

修改后的查询代码 #

SELECT 
    pn.TITLE,
    pn.FIRST_NAME,
    pn.LAST_NAME,
    pn.FULL_NAME,
    pap.PERSON_NUMBER,
    pni.NATIONAL_IDENTIFIER_NUMBER,
    pni.LEGISLATION_CODE,
    TO_CHAR(pap.EFFECTIVE_START_DATE,'DD-MM-YYYY') AS EFFECTIVE_START_DATE,
    TO_CHAR(pap.EFFECTIVE_END_DATE,'DD-MM-YYYY') AS EFFECTIVE_END_DATE,
    TO_CHAR(pp.DATE_OF_BIRTH,'DD-MM-YYYY') AS DOB,
    pa.ASSIGNMENT_TYPE,
    pe.EMAIL_ADDRESS,
    ppn.PHONE_NUMBER,
    cs.SALARY,
    cs.SALARY_UPDATE_DATE
FROM PER_ALL_PEOPLE_F pap
JOIN PER_PERSONS pp ON pap.PERSON_ID = pp.PERSON_ID
JOIN PER_PERSON_NAMES_F pn ON pp.PERSON_ID = pn.PERSON_ID
JOIN PER_ALL_ASSIGNMENTS_M pa ON pn.PERSON_ID = pa.PERSON_ID
JOIN PER_NATIONAL_IDENTIFIERS pni ON pa.PERSON_ID = pni.PERSON_ID
JOIN PER_EMAIL_ADDRESSES pe ON pni.PERSON_ID = pe.PERSON_ID
-- 筛选每个员工的最新薪资记录
JOIN (
    SELECT 
        PERSON_ID,
        TRUNC(SALARY_AMOUNT,2) AS SALARY,
        LAST_UPDATE_DATE AS SALARY_UPDATE_DATE,
        ROW_NUMBER() OVER(PARTITION BY PERSON_ID ORDER BY LAST_UPDATE_DATE DESC) AS rn
    FROM CMP_SALARY
) cs ON pe.PERSON_ID = cs.PERSON_ID AND cs.rn = 1
-- 处理电话表重复：取每个员工最新的电话记录
JOIN (
    SELECT 
        PERSON_ID,
        PHONE_NUMBER,
        ROW_NUMBER() OVER(PARTITION BY PERSON_ID ORDER BY LAST_UPDATE_DATE DESC) AS rn
    FROM PER_PHONES
) ppn ON cs.PERSON_ID = ppn.PERSON_ID AND ppn.rn = 1
ORDER BY pap.PERSON_NUMBER;

关键修改说明 #

1. 薪资记录精准筛选：

   • 使用ROW_NUMBER()窗口函数，按PERSON_ID分组、LAST_UPDATE_DATE倒序排序，为每条薪资记录标记序号；
   • 通过cs.rn = 1确保只保留每个员工的最新薪资记录，同时保证薪资金额与更新日期一一对应。

2. 关联表重复处理：

   • 对PER_PHONES采用同样的窗口函数逻辑，取每个员工的最新电话记录；
   • 若PER_EMAIL_ADDRESSES也存在多记录，可参照电话表的方式筛选单条，或用LISTAGG(pe.EMAIL_ADDRESS, ', ') WITHIN GROUP (ORDER BY pe.EMAIL_ADDRESS)合并所有邮箱。

3. 冗余条件清理：

   • 移除原查询中PER_NATIONAL_IDENTIFIERS.NATIONAL_IDENTIFIER_NUMBER IN (SELECT DISTINCT ...)的冗余条件，因为表连接已确保数据存在，该条件无实际过滤作用。

4. 代码可读性优化：

   • 为所有表添加短别名，简化代码结构，提升维护效率。

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