SQL优化：针对特定ID高效获取不大于目标日期的最近匹配日期

嘿，这个问题我之前帮不少人解决过——笛卡尔积的方式在数据量大的时候完全是灾难，因为它会把两个表同ID的所有时间组合都生成出来，数据量直接爆炸。给你几个高效的优化方案，亲测好用：

方法1：窗口函数法（通用大多数主流数据库） #

这种方法适合PostgreSQL、MySQL 8.0+、SQL Server等支持窗口函数的数据库，核心是先关联符合条件的记录，再给每个time1对应的time2排序取最近的那条。

WITH ranked_matches AS (
    SELECT 
        h.ID,
        h.time1,
        m.time2,
        -- 按ID和time1分组，把符合条件的time2从晚到早排序
        ROW_NUMBER() OVER (
            PARTITION BY h.ID, h.time1 
            ORDER BY m.time2 DESC
        ) AS rn
    FROM Table1 h
    JOIN Table2 m 
        ON h.ID = m.ID 
        AND m.time2 <= h.time1
)
-- 取每个time1对应的第一条（也就是最近的time2）
SELECT ID, time1, time2
FROM ranked_matches
WHERE rn = 1;

方法2：LATERAL JOIN/APPLY（更高效的定向查询） #

这种方法比窗口函数更高效，因为它会为Table1的每一行单独查询符合条件的最近time2，不会生成所有可能的匹配对，中间数据量小很多。

PostgreSQL版本 #

SELECT 
    h.ID,
    h.time1,
    m.time2
FROM Table1 h
LEFT JOIN LATERAL (
    -- 只取同ID下<=当前time1的最晚time2
    SELECT time2
    FROM Table2 m
    WHERE m.ID = h.ID 
      AND m.time2 <= h.time1
    ORDER BY time2 DESC
    LIMIT 1
) m ON true;

SQL Server版本 #

SELECT 
    h.ID,
    h.time1,
    m.time2
FROM Table1 h
OUTER APPLY (
    -- 只取同ID下<=当前time1的最晚time2
    SELECT TOP 1 time2
    FROM Table2 m
    WHERE m.ID = h.ID 
      AND m.time2 <= h.time1
    ORDER BY time2 DESC
) m;

注：如果只需要有匹配结果的记录，把LEFT JOIN/OUTER APPLY换成INNER JOIN/CROSS APPLY即可。

必做优化：添加索引 #

不管用哪种方法，索引都是提速的核心！给Table2创建复合索引，让数据库能快速定位到目标记录：

-- 按ID分组，time2倒序排列，完美匹配我们的查询逻辑
CREATE INDEX idx_table2_id_time2 ON Table2 (ID, time2 DESC);

如果Table1的查询也很频繁，也可以给它加个辅助索引：

CREATE INDEX idx_table1_id_time1 ON Table1 (ID, time1);

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