Oracle SQL：高效运用LAST_VALUE与OVER函数计算联合版本

核心思路是通过时间戳匹配每个Table1版本对应的最新Table2版本（即同ID下，Table2记录的时间不晚于当前Table1记录时间的最大版本），再计算组合版本。这种方式无需临时表，且通过合理索引可保证高效批量执行。

高效查询语句（Oracle 12c+） #

使用LATERAL JOIN实现逐行精准匹配，确保每个Table1记录关联对应时间点的最新Table2版本：

SELECT
    t1.ID,
    t1.TABLE1_VER,
    NVL(t2.TABLE2_VER, 0) AS TABLE2_VER,
    t1.TABLE1_VER + NVL(t2.TABLE2_VER, 0) AS COMBINED_VER,
    t1.AUDIT_DATETIME AS TABLE1_AUDIT_TIME,
    NVL(t2.AUDIT_DATETIME, NULL) AS TABLE2_AUDIT_TIME
FROM TABLE1 t1
LEFT JOIN LATERAL (
    -- 取同ID下时间不晚于当前Table1记录的最新Table2版本
    SELECT TABLE2_VER, AUDIT_DATETIME
    FROM TABLE2 t2
    WHERE t2.ID = t1.ID
      AND t2.AUDIT_DATETIME <= t1.AUDIT_DATETIME
    ORDER BY t2.TABLE2_VER DESC -- 版本号递增，最大版本即为最新
    FETCH FIRST 1 ROW ONLY
) t2 ON 1=1
ORDER BY t1.ID, t1.TABLE1_VER;

兼容低版本Oracle（11g及以下） #

若无法使用LATERAL JOIN，可借助分析函数实现相同逻辑：

SELECT
    t1.ID,
    t1.TABLE1_VER,
    NVL(t2.TABLE2_VER, 0) AS TABLE2_VER,
    t1.TABLE1_VER + NVL(t2.TABLE2_VER, 0) AS COMBINED_VER,
    t1.AUDIT_DATETIME AS TABLE1_AUDIT_TIME,
    NVL(t2.AUDIT_DATETIME, NULL) AS TABLE2_AUDIT_TIME
FROM TABLE1 t1
LEFT JOIN (
    SELECT
        ID,
        TABLE2_VER,
        AUDIT_DATETIME,
        -- 按时间降序排名，同ID下最新的记录排第一
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AUDIT_DATETIME DESC) AS rn
    FROM TABLE2
) t2 ON t2.ID = t1.ID
    AND t2.AUDIT_DATETIME <= t1.AUDIT_DATETIME
    AND t2.rn = 1
ORDER BY t1.ID, t1.TABLE1_VER;

性能优化建议 #

为避免全表扫描，需创建以下索引提升查询效率：

• Table1索引：CREATE INDEX idx_table1_id_audit ON TABLE1(ID, AUDIT_DATETIME, TABLE1_VER);
• Table2索引：CREATE INDEX idx_table2_id_audit_ver ON TABLE2(ID, AUDIT_DATETIME DESC, TABLE2_VER);

示例数据验证 #

针对提供的示例数据，执行上述查询后将得到预期结果：

注：Table2后续版本（VER=2、3）的时间晚于Table1_VER=3的时间，因此该Table1版本无法关联到这些后续版本，完全符合时间匹配的逻辑。

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