寻求基于Table A匹配Table C最近周期盘点事件的简化SQL JOIN方案

问题明确 #

需要为Table A中符合条件的每条库存调整记录，匹配Table C中相同LPID、时间戳不晚于A记录时间的最近周期盘点事件，同时保留A的所有记录（无匹配时C字段为NULL）。

固定筛选条件 #

• A.facility = 'FACID'
• A.WHENOCCURRED > '23-DEC-22'
• A.ADJREASONABBREV = 'CYCLE COUNTS'

方案1：窗口函数 + 左连接（通用主流方案） #

适合绝大多数支持窗口函数的数据库（Oracle、SQL Server、PostgreSQL等），逻辑清晰且效率较高：

SELECT 
    A.*,
    C.WHENOCCURRED AS latest_cycle_count_time,
    C.product_id AS cycle_count_product_id -- 按需添加Table C的其他字段
FROM 
    Table_A A
LEFT JOIN (
    SELECT 
        LPID,
        WHENOCCURRED,
        product_id,
        -- 按LPID分组，将同LPID的盘点记录按时间倒序排名，最近的排第1
        ROW_NUMBER() OVER (PARTITION BY LPID ORDER BY WHENOCCURRED DESC) AS rn
    FROM 
        Table_C
    -- 若Table C有周期盘点专属标识，需在此添加筛选，比如：
    -- WHERE C.event_type = 'CYCLE_COUNT'
) C 
    ON A.LPID = C.LPID 
    AND C.WHENOCCURRED <= A.WHENOCCURRED -- 确保盘点时间早于/等于调整时间
    AND C.rn = 1
WHERE 
    A.facility = 'FACID'
    -- 建议显式转换日期格式，避免隐式转换问题
    AND A.WHENOCCURRED > TO_DATE('23-DEC-22', 'DD-MON-RR')
    AND A.ADJREASONABBREV = 'CYCLE COUNTS';

方案说明 #

1. 子查询先对Table C按LPID分组，给每条记录按时间戳倒序生成排名，最近的盘点记录rn=1
2. 左连接时同时匹配LPID、时间范围，并只取排名第一的记录，确保每个A记录对应唯一的最近盘点
3. 自动保留A的所有符合条件的记录，无匹配时C的字段返回NULL

方案2：关联子查询（兼容老版本数据库） #

如果数据库不支持窗口函数（如Oracle 11g及更早），可以用关联子查询实现：

SELECT 
    A.*,
    -- 获取同LPID下最近的盘点时间
    (SELECT MAX(WHENOCCURRED)
     FROM Table_C C
     WHERE C.LPID = A.LPID
       AND C.WHENOCCURRED <= A.WHENOCCURRED) AS latest_cycle_count_time,
    -- 若需要Table C的其他字段，可嵌套子查询匹配对应时间的记录
    (SELECT product_id
     FROM Table_C C
     WHERE C.LPID = A.LPID
       AND C.WHENOCCURRED = (
           SELECT MAX(WHENOCCURRED)
           FROM Table_C C2
           WHERE C2.LPID = A.LPID
             AND C2.WHENOCCURRED <= A.WHENOCCURRED
       )) AS cycle_count_product_id
FROM 
    Table_A A
WHERE 
    A.facility = 'FACID'
    AND A.WHENOCCURRED > TO_DATE('23-DEC-22', 'DD-MON-RR')
    AND A.ADJREASONABBREV = 'CYCLE COUNTS';

方案说明 #

• 外层子查询直接为每个A记录找到同LPID下时间不晚于A的最大时间戳（即最近盘点）
• 若需其他C字段，通过嵌套子查询匹配对应时间的记录即可；若同LPID同时间有多个盘点记录，需额外处理去重

方案3：LATERAL JOIN（简洁高效，支持的数据库） #

适合Oracle 12c+、PostgreSQL、SQL Server 2016+等支持LATERAL JOIN的数据库，语法最简洁：

SELECT 
    A.*,
    C.*
FROM 
    Table_A A
LEFT JOIN LATERAL (
    SELECT *
    FROM Table_C C
    WHERE C.LPID = A.LPID
      AND C.WHENOCCURRED <= A.WHENOCCURRED
    ORDER BY C.WHENOCCURRED DESC
    FETCH FIRST 1 ROW ONLY -- Oracle写法，PostgreSQL用LIMIT 1；SQL Server用OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY
) C ON 1=1
WHERE 
    A.facility = 'FACID'
    AND A.WHENOCCURRED > TO_DATE('23-DEC-22', 'DD-MON-RR')
    AND A.ADJREASONABBREV = 'CYCLE COUNTS';

方案说明 #

• LATERAL JOIN允许子查询直接引用主查询的字段，针对每个A记录，直接筛选出符合条件的最近一条C记录
• 执行效率优于嵌套子查询，语法更直观

性能优化建议 #

• 给Table A创建复合索引：(facility, ADJREASONABBREV, WHENOCCURRED, LPID)，加速筛选和连接
• 给Table C创建复合索引：(LPID, WHENOCCURRED)，加速同LPID下的时间筛选

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