如何在另一表无当前月行时，关联最近可用历史月进行表连接？

当Table A的WORKING_ENDOFMONTH在Table B中无对应ENDOFMONTH时，需要匹配该用户在Table B中最近的历史月份数据进行关联。以下是具体表结构及期望结果：

Table A #

Table B #

期望结果表 #

解决方案 #

方法1：LEFT JOIN + 窗口函数ROW_NUMBER() #

该方法兼容绝大多数关系型数据库，逻辑清晰易理解：

1. 关联Table A与Table B，筛选出用户匹配且B表月份不晚于A表目标月份的所有记录
2. 对每个A表行的关联结果，按月份倒序排序，取排序第一的记录（即最近的历史月份）

SELECT 
    t.USERID,
    t.WORKING_ENDOFMONTH,
    t.HOURS
FROM (
    SELECT 
        a.USERID,
        a.WORKING_ENDOFMONTH,
        b.HOURS,
        -- 按用户和目标月份分组，对关联的B表记录按月份倒序编号
        ROW_NUMBER() OVER (
            PARTITION BY a.USERID, a.WORKING_ENDOFMONTH 
            ORDER BY b.ENDOFMONTH DESC
        ) AS rn
    FROM TableA a
    LEFT JOIN TableB b 
        ON a.USERID = b.USERID 
        AND b.ENDOFMONTH <= a.WORKING_ENDOFMONTH
) t
-- 只保留每个分组中编号为1的记录（最近的历史月份）
WHERE t.rn = 1;

方法2：LATERAL JOIN / CROSS APPLY #

该方法性能更优，直接对每个A表行查询符合条件的最近一条B表记录，避免了冗余关联：

PostgreSQL / MySQL 8.0+ 写法

SELECT 
    a.USERID,
    a.WORKING_ENDOFMONTH,
    b.HOURS
FROM TableA a
LEFT JOIN LATERAL (
    -- 对当前A表行，查询用户匹配且月份不晚于目标月份的最近一条记录
    SELECT HOURS
    FROM TableB b
    WHERE b.USERID = a.USERID 
        AND b.ENDOFMONTH <= a.WORKING_ENDOFMONTH
    ORDER BY b.ENDOFMONTH DESC
    LIMIT 1
) b ON true;

SQL Server 写法

SELECT 
    a.USERID,
    a.WORKING_ENDOFMONTH,
    b.HOURS
FROM TableA a
OUTER APPLY (
    SELECT TOP 1 HOURS
    FROM TableB b
    WHERE b.USERID = a.USERID 
        AND b.ENDOFMONTH <= a.WORKING_ENDOFMONTH
    ORDER BY b.ENDOFMONTH DESC
) b;

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