如何在PostgreSQL多表关联时筛选基于最新日期的唯一记录？

针对TABLE_D存在多条关联记录、需提取DATE_ACTION最新值的需求，这里提供两种PostgreSQL中实用的解决方案：

方法一：使用窗口函数ROW_NUMBER() #

通过窗口函数给TABLE_D中同一JOB+SUFFIX的记录按DATE_ACTION降序编号，仅保留编号为1的最新记录，再与主查询关联：

SELECT 
    A.ORDER_NO, 
    A.RECORD_NO, 
    B.PART,  
    A.MUST_DLVR_BY_DATE, 
    C.JOB, 
    C.SUFFIX, 
    D_latest.DATE_ACTION, 
    A.DATE_SHIPPED
FROM TABLE_A A
INNER JOIN TABLE_B B 
    ON A.ORDER_NO = B.ORDER_NO 
    AND A.RECORD_NO = B.RECORD_NO
INNER JOIN TABLE_C C 
    ON A.ORDER_NO = C.SALES_ORDER 
    AND LEFT(A.RECORD_NO, 3) = C.SALES_ORDER_LINE
INNER JOIN (
    SELECT 
        JOB, 
        SUFFIX, 
        DATE_ACTION,
        ROW_NUMBER() OVER (PARTITION BY JOB, SUFFIX ORDER BY DATE_ACTION DESC) AS rn
    FROM TABLE_D
    WHERE DATE_ACTION <> '' 
      AND code_transaction = 'J52'
) D_latest 
    ON C.JOB = D_latest.JOB 
    AND C.SUFFIX = D_latest.SUFFIX
    AND D_latest.rn = 1
WHERE 
    B.DATE_SHIP IN ('', '000000') 
    AND A.must_dlvr_by_date <> '00000000'
ORDER BY A.ORDER_NO, A.RECORD_NO;

说明： #

• 子查询中PARTITION BY JOB, SUFFIX按关联字段分组，ORDER BY DATE_ACTION DESC让最新记录排在首位
• ROW_NUMBER()为每组内的记录编号，取rn=1即可得到每组的最新记录
• 原查询中LEFT JOIN TABLE_D后通过WHERE过滤D的非空值，实际等同于INNER JOIN，这里直接用INNER JOIN关联子查询更贴合实际逻辑

方法二：使用LATERAL JOIN（横向关联） #

PostgreSQL的LATERAL JOIN允许关联子查询引用主查询字段，可为每个C表记录精准匹配TABLE_D的最新记录：

SELECT 
    A.ORDER_NO, 
    A.RECORD_NO, 
    B.PART,  
    A.MUST_DLVR_BY_DATE, 
    C.JOB, 
    C.SUFFIX, 
    D.DATE_ACTION, 
    A.DATE_SHIPPED
FROM TABLE_A A
INNER JOIN TABLE_B B 
    ON A.ORDER_NO = B.ORDER_NO 
    AND A.RECORD_NO = B.RECORD_NO
INNER JOIN TABLE_C C 
    ON A.ORDER_NO = C.SALES_ORDER 
    AND LEFT(A.RECORD_NO, 3) = C.SALES_ORDER_LINE
LEFT JOIN LATERAL (
    SELECT DATE_ACTION
    FROM TABLE_D
    WHERE JOB = C.JOB 
      AND SUFFIX = C.SUFFIX
      AND DATE_ACTION <> '' 
      AND code_transaction = 'J52'
    ORDER BY DATE_ACTION DESC
    LIMIT 1
) D ON true
WHERE 
    B.DATE_SHIP IN ('', '000000') 
    AND A.must_dlvr_by_date <> '00000000'
    AND D.DATE_ACTION IS NOT NULL -- 保留有匹配D记录的结果，与原查询逻辑一致
ORDER BY A.ORDER_NO, A.RECORD_NO;

说明： #

• LATERAL子查询可直接使用主查询中C表的JOB和SUFFIX字段，精准匹配当前记录
• ORDER BY DATE_ACTION DESC LIMIT 1直接获取该组的最新记录
• 若需保留无匹配D记录的结果，可去掉WHERE中的D.DATE_ACTION IS NOT NULL，恢复原LEFT JOIN逻辑

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