Oracle多表关联查询如何按ID去重获取每个参与者最新记录

重复原因 #

你当前的查询仅筛选了两张子表的最大日期，但没有处理同一申请人在最大日期下存在多条子表记录的场景：

• 同一个申请人的CASE_NOTES表在最大service_date可能存在多条内容不同的记录
• 同一个申请人的WIA_TRANSACTION表在最大service_date可能存在多条交易记录
• 也可能WIA_REG表中同一个申请人存在多条符合过滤条件的有效注册记录
  两张子表的多条记录关联后会产生笛卡尔积，最终导致同一ID返回多条重复结果。

优化方案 #

使用Oracle支持的ROW_NUMBER()窗口函数对子表按申请人分组排序，每个申请人仅取最新的1条子表记录，即可解决重复问题，参考查询语句如下：

SELECT 
    a.ID as ID,
    a.USERNAME as USERNAME,
    w.LAST_NAME as LAST_NAME,
    w.FIRST_NAME as FIRST_NAME,
    w.BIRTH_DATE as BIRTH_DATE,
    w.PROGRAM_CODE as PROGRAM_CODE,
    w.REGION_CODE as REGION_CODE,
    w.REG_DATE as REG_DATE,
    w.STATUS_CD as STATUS_CD,
    w.STATUS_DATE as STATUS_DATE,
    c.SERVICE_DATE AS LAST_CASE_NOTE_SERVICE_DATE,
    c.SUBJECT AS CASE_NOTE_SUBJECT,
    c.NOTES AS CASE_NOTE,
    t.SERVICE_CODE AS SERVICE_CODE,
    t.SERVICE_DATE AS SERVICE_DATE
FROM 
    -- 先过滤WIA_REG符合条件的记录，若同一申请人有多条注册记录也可在此处加窗口函数处理
    (SELECT * FROM WIA_REG 
     WHERE STATUS_CD ='AC'
       AND PROGRAM_CODE IN('ADULT','DW')
       AND STATUS_DATE BETWEEN DATE '2018-01-01' AND DATE '2019-12-31') w
JOIN APPLICANT a ON a.ID = w.APPLICANT_ID
-- 关联最新的1条CASE_NOTES记录
JOIN (
    SELECT 
        APPLICANT_ID, SERVICE_DATE, SUBJECT, NOTES,
        ROW_NUMBER() OVER(PARTITION BY APPLICANT_ID ORDER BY SERVICE_DATE DESC) rn
    FROM CASE_NOTES
) c ON c.APPLICANT_ID = a.ID AND c.rn = 1
-- 关联最新的1条WIA_TRANSACTION记录
JOIN (
    SELECT 
        APPLICANT_ID, SERVICE_CODE, SERVICE_DATE,
        ROW_NUMBER() OVER(PARTITION BY APPLICANT_ID ORDER BY SERVICE_DATE DESC) rn
    FROM WIA_TRANSACTION
) t ON t.APPLICANT_ID = a.ID AND t.rn = 1
ORDER BY a.USERNAME ASC;

逻辑说明 #

• 对CASE_NOTES和WIA_TRANSACTION分别做子查询，按APPLICANT_ID分组、SERVICE_DATE降序排序，rn=1的就是该申请人最新的一条记录，即使同一天有多条记录也只会返回一条
• 如果需要保留没有CASE_NOTES或没有交易记录的申请人，可以把对应的JOIN改成LEFT JOIN
• 如果WIA_REG表中同一申请人可能存在多条符合条件的记录，也可以参照子表的处理方式给WIA_REG加窗口函数，按你需要的排序规则取最新的一条注册记录即可

补充：如果确认重复记录所有字段内容完全一致，也可以临时在最外层的SELECT后加DISTINCT关键词快速去重，但窗口函数方案从逻辑根源避免笛卡尔积，是更稳定的解决方案。

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