如何获取另一表中早于当前表日期的最近记录（无则返回NULL）

假设我们有两张核心表：

• main_table：存储主业务记录，包含字段id（主键标识）、main_date（当前记录的基准日期）
• related_table：存储关联的历史记录，包含字段link_id（与主表关联的匹配键）、related_date（历史记录的日期）、target_value（需要提取的目标数据）

方法1：关联子查询（兼容多数数据库） #

逻辑直观，适配几乎所有支持子查询的数据库：

SELECT
    m.id,
    m.main_date,
    -- 筛选关联表中日期早于当前主表日期的记录，按日期倒序取第一条的值
    (SELECT r.target_value
     FROM related_table r
     WHERE r.link_id = m.id 
       AND r.related_date < m.main_date
     ORDER BY r.related_date DESC
     LIMIT 1) AS latest_value
FROM main_table m;

当无符合条件的关联记录时，子查询自动返回NULL，完全匹配需求。

方法2：窗口函数（适合MySQL 8+、PostgreSQL、SQL Server等） #

数据量较大时，窗口函数的性能表现更优：

WITH ranked_related AS (
    SELECT
        m.id AS main_id,
        m.main_date,
        r.target_value,
        -- 按主表ID分组，关联日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY m.id ORDER BY r.related_date DESC) AS rn
    FROM main_table m
    LEFT JOIN related_table r 
        ON r.link_id = m.id 
        AND r.related_date < m.main_date
)
SELECT
    main_id AS id,
    main_date,
    -- 仅保留每组排名第一的记录值
    CASE WHEN rn = 1 THEN target_value ELSE NULL END AS latest_value
FROM ranked_related
GROUP BY main_id, main_date;

通过LEFT JOIN确保主表所有记录都被保留，无符合条件的关联记录时target_value自然为NULL。

注意事项 #

• 请根据实际业务场景调整关联字段（比如把link_id = m.id替换为你的真实匹配条件）
• 如果需要处理关联表中存在同日期多条记录的情况，可将ROW_NUMBER()替换为RANK()，后续可结合MAX(target_value)等聚合函数处理重复值

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