Oracle SQL 基于两表不同最新日期实现三表关联的查询问题

先修正你原有SQL的笔误（SUPPRODUCT为拼写错误，应为SUBPRODUCT），再补充Issues表的关联逻辑：假设Issues表通过产品ID（与PRODUCT.P_ID/SUBPRODUCT.SP_PRODUCT_ID对齐）关联，且通过日期字段（下文示例为ISSUE_UPDATE_DATE，可替换为你表中实际的状态更新时间字段）判断最新状态，完整实现代码如下：

SELECT
    PRODUCT.P_NAME,
    SUBPRODUCT.SP_PRODUCT_ID, 
    SUBPRODUCT.SP_NAME, 
    SUBPRODUCT.SP_ID, 
    SUBPRODUCT.SP_VALUE_DATE,
    ISSUES.STATUS_FLAG
FROM SUBPRODUCT
-- 关联主产品表
LEFT OUTER JOIN PRODUCT 
    ON PRODUCT.P_ID = SUBPRODUCT.SP_PRODUCT_ID
-- 关联子产品最新日期的过滤子查询
JOIN (
    SELECT SP_PRODUCT_ID, MAX(SP_VALUE_DATE) AS latestdate 
    FROM SUBPRODUCT 
    GROUP BY SP_PRODUCT_ID
) sub 
    ON sub.SP_PRODUCT_ID = SUBPRODUCT.SP_PRODUCT_ID 
    AND sub.latestDate = SUBPRODUCT.SP_VALUE_DATE
-- 新增关联：每个主产品的最新问题状态子查询
LEFT JOIN (
    SELECT 
        I_PRODUCT_ID, 
        STATUS_FLAG,
        ROW_NUMBER() OVER (PARTITION BY I_PRODUCT_ID ORDER BY ISSUE_UPDATE_DATE DESC) AS rn
    FROM ISSUES
) latest_issue 
    ON latest_issue.I_PRODUCT_ID = PRODUCT.P_ID 
    AND latest_issue.rn = 1;

逻辑说明 #

• 新增的latest_issue子查询用窗口函数ROW_NUMBER()按产品ID分组，按状态更新时间倒序排序，取每个产品排序为1的记录即为最新状态
• 用LEFT JOIN关联Issues表是为了避免无状态记录的产品被过滤，若不需要保留无状态的产品可改为JOIN
• 若Issues表是和子产品ID关联而非主产品ID，只需将关联条件改为latest_issue.I_SP_ID = SUBPRODUCT.SP_ID，同时修改子查询的分组字段即可

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