如何在SQL中按最近的前置日期获取匹配记录？

数据表结构 #

Table1（分数记录表） #

id     score    score_date
---------------------------
1234     99     2025-01-23
5678    153     2025-01-23
9101    121     2025-01-23
1234    103     2025-01-30
5678    155     2025-01-30
9101    126     2025-01-30
1234    101     2025-02-06
5678    150     2025-02-06
9101    124     2025-02-06
1234    100     2025-02-13
5678    152     2025-02-13

Table2（列表分配表） #

id      list    list_date
--------------------------
1234    LISTA   2025-01-27
5678    LISTB   2025-01-30
9101    LISTC   2025-02-14

需求说明 #

通过ID关联两张表，将Table1中的score与Table2中的list按最近的前置日期匹配，必须满足list_date < score_date（同日期记录不匹配）。期望输出如下：

id  score   list    list_date
-------------------------------
1234    99  LISTA   2025-01-27
5678    155 LISTB   2025-01-30
9101    124 LISTC   2025-02-14

（注：原期望输出中存在日期笔误，已修正为与数据表对应逻辑一致的结果）

此前尝试的问题 #

曾使用区间过滤语句：and interval('days', t2.list_date-t1.score_date) between 1 and 6，该方案存在两个核心问题：

• 通用性差：硬编码固定日期范围，无法适配不同业务场景的时间间隔需求
• 覆盖不全：无法捕获像ID 9101这种列表创建后7天内无新score记录的场景，会遗漏合法匹配

解决方案 #

采用窗口函数+关联筛选或**横向连接（LATERAL JOIN）**的方式，完全基于「最近前置日期」逻辑匹配，无需硬编码区间。

方案1：窗口函数实现（通用SQL，支持多数现代数据库） #

WITH ranked_matches AS (
    SELECT
        t1.id,
        t1.score,
        t2.list,
        t2.list_date,
        -- 按每个列表记录分组，将符合条件的分数记录按日期降序排名
        ROW_NUMBER() OVER (
            PARTITION BY t2.id, t2.list_date
            ORDER BY t1.score_date DESC
        ) AS rank_num
    FROM Table1 t1
    JOIN Table2 t2
        ON t1.id = t2.id
        AND t1.score_date < t2.list_date -- 仅保留列表日期之前的分数记录
)
-- 取每个列表对应的最近一条分数记录
SELECT id, score, list, list_date
FROM ranked_matches
WHERE rank_num = 1;

方案2：横向连接实现（适配PostgreSQL/MySQL 8+/SQL Server） #

SELECT
    t2.id,
    t1.score,
    t2.list,
    t2.list_date
FROM Table2 t2
-- 对每个列表记录，关联其对应ID下最近的前置分数记录
JOIN LATERAL (
    SELECT score
    FROM Table1
    WHERE id = t2.id AND score_date < t2.list_date
    ORDER BY score_date DESC
    LIMIT 1
) t1 ON TRUE;

方案优势

• 精准匹配：严格遵循list_date < score_date规则，自动选取每个列表对应的最近前置日期的分数记录
• 通用性强：无需硬编码日期区间，适配所有符合逻辑的业务场景，包括列表创建后多日无新分数的情况
• 可维护性高：逻辑清晰，后续需求变更（如调整匹配规则）只需修改排序或过滤条件即可

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