如何关联主表记录时间点之前的最新关联表记录

我平时经常碰到类似“怎么关联两张表的最新记录”的问题，常规方案其实挺成熟，但这次你的需求确实不一样——常规方法满足不了，我来拆解下怎么处理。

常规方案的局限 #

平时我常用的思路是：先通过子查询拿到每个ID对应的最大记录时间，再关联回原表匹配ID和时间，拿到最新的那条记录，还能在子查询的WHERE里加额外过滤条件。比如这样：

-- 常规关联最新记录的写法
SELECT m.*, s.*
FROM main_table m
JOIN (
    SELECT id, MAX(record_date) AS latest_date
    FROM second_table
    -- 可添加额外过滤条件，比如 WHERE status = 'active'
    GROUP BY id
) s_latest ON m.id = s_latest.id
JOIN second_table s ON s.id = s_latest.id AND s.record_date = s_latest.latest_date;

但这个方案的问题在于：它只能拿到主表对应ID的全局最新子表记录，没法做到“针对主表的每一条历史记录，关联这条记录时间点之前的子表最新记录”——这正是你当前需要的场景。

针对当前场景的解决方案 #

要实现“每条主表历史记录对应其时间前的子表最新记录”，推荐用LATERAL JOIN（PostgreSQL）/OUTER APPLY（SQL Server），或者窗口函数的方式，核心是针对主表的每一行单独做筛选。

方法1：用LATERAL JOIN（PostgreSQL） #

这种方式最直观，相当于给主表的每一行单独执行一次子查询，筛选出符合时间条件的最新子表记录：

SELECT m.*, s.*
FROM main_table m
LEFT JOIN LATERAL (
    SELECT *
    FROM second_table s
    WHERE s.id = m.id
      AND s.record_date <= m.history_date -- 关键：子表记录时间不晚于主表这条历史的时间
    ORDER BY s.record_date DESC
    LIMIT 1 -- 只取最新的那条
) s ON true;

如果是SQL Server，把LATERAL换成OUTER APPLY即可，逻辑完全一致。

方法2：用窗口函数标记排序 #

如果你用的数据库支持窗口函数（比如MySQL 8+、PostgreSQL、SQL Server），也可以先给子表的记录按ID分组、按时间倒序排序，再关联主表筛选出符合时间条件且排序为1的记录：

WITH ranked_sub_records AS (
    SELECT 
        s.*,
        ROW_NUMBER() OVER (
            PARTITION BY s.id 
            ORDER BY s.record_date DESC
        ) AS record_rank
    FROM second_table s
    -- 先关联主表过滤出时间符合条件的子表记录
    JOIN main_table m ON s.id = m.id AND s.record_date <= m.history_date
)
SELECT m.*, rs.*
FROM main_table m
LEFT JOIN ranked_sub_records rs 
    ON m.id = rs.id 
    AND rs.record_rank = 1;

这两种方法都能精准满足你的需求：拿到主表的所有历史记录，同时每条记录都关联上它时间点之前子表的最新那条数据。

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