如何基于创建日期匹配两张表中最相近的关联记录？

需求 #

我有两张通过user_id关联的表，需满足以下关联条件：

• 仅关联同一用户的记录；
• 为table_1的每条记录匹配table_2中created_at不早于当前记录的最近对应记录，若不存在则返回null。

表结构 #

table_1 #

table_2 #

预期结果 #

解决方案 #

方法1：兼容多数数据库（使用窗口函数） #

适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库：

WITH ranked_matches AS (
    SELECT
        t1.id AS t1_id,
        t2.created_at,
        ROW_NUMBER() OVER (
            PARTITION BY t1.id
            ORDER BY t2.created_at ASC
        ) AS rn
    FROM table_1 t1
    LEFT JOIN table_2 t2
        ON t1.user_id = t2.user_id
        AND t2.created_at >= t1.created_at
)
SELECT
    t1.id,
    t1.user_id,
    t1.name,
    t1.created_at,
    rm.created_at AS t2_created_at
FROM table_1 t1
LEFT JOIN ranked_matches rm
    ON t1.id = rm.t1_id
    AND rm.rn = 1;

方法2：PostgreSQL专用（LATERAL JOIN） #

写法简洁直观：

SELECT
    t1.id,
    t1.user_id,
    t1.name,
    t1.created_at,
    t2.created_at AS t2_created_at
FROM table_1 t1
LEFT JOIN LATERAL (
    SELECT created_at
    FROM table_2
    WHERE user_id = t1.user_id
      AND created_at >= t1.created_at
    ORDER BY created_at ASC
    LIMIT 1
) t2 ON true;

方法3：SQL Server专用（OUTER APPLY） #

与PostgreSQL的LATERAL JOIN逻辑一致：

SELECT
    t1.id,
    t1.user_id,
    t1.name,
    t1.created_at,
    t2.created_at AS t2_created_at
FROM table_1 t1
OUTER APPLY (
    SELECT TOP 1 created_at
    FROM table_2
    WHERE user_id = t1.user_id
      AND created_at >= t1.created_at
    ORDER BY created_at ASC
) t2;

方法4：兼容低版本MySQL（无窗口函数） #

通过子查询实现，适用于MySQL 5.x等不支持窗口函数的版本：

SELECT
    t1.id,
    t1.user_id,
    t1.name,
    t1.created_at,
    (
        SELECT created_at
        FROM table_2 t2
        WHERE t2.user_id = t1.user_id
          AND t2.created_at >= t1.created_at
        ORDER BY t2.created_at ASC
        LIMIT 1
    ) AS t2_created_at
FROM table_1 t1;

所有方法均会为table_1的每条记录匹配同一用户下table_2中时间最近且不早于当前记录的条目，无匹配时返回null，完全符合预期结果。

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