SQL Server：如何按最近日期关联表并添加参考费用列？

可以使用**窗口函数ROW_NUMBER()**结合日期差计算来实现“匹配最近日期”的关联需求，以下是针对SQL Server的具体实现：

SELECT 
    a.ID_A,
    a.ID_B,
    a.Donation_date,
    a.Donation_amount,
    b.Ref_Expense_date,
    b.Ref_expense_amount
FROM TableA a
JOIN (
    SELECT 
        ID_A,
        ID_B,
        Expense_date AS Ref_Expense_date,
        Expense_amount AS Ref_expense_amount,
        -- 计算当前费用日期与每个捐赠日期的绝对天数差
        ABS(DATEDIFF(day, CONVERT(date, a_inner.Donation_date, 103), CONVERT(date, b_inner.Expense_date, 103))) AS date_diff,
        -- 按ID和捐赠日期分组，按日期差升序排序，标记最近的费用记录
        ROW_NUMBER() OVER (
            PARTITION BY b_inner.ID_A, b_inner.ID_B, a_inner.Donation_date 
            ORDER BY ABS(DATEDIFF(day, CONVERT(date, a_inner.Donation_date, 103), CONVERT(date, b_inner.Expense_date, 103))) ASC
        ) AS rn
    FROM TableB b_inner
    JOIN TableA a_inner ON b_inner.ID_A = a_inner.ID_A AND b_inner.ID_B = a_inner.ID_B
) b ON a.ID_A = b.ID_A 
   AND a.ID_B = b.ID_B 
   AND a.Donation_date = b.Donation_date
   AND b.rn = 1;

关键逻辑说明： #

• 日期格式转换：用CONVERT(date, 日期字段, 103)将dd/mm/yyyy格式的字符串日期转为SQL Server可识别的date类型，保证日期计算的准确性。
• 日期差计算：通过DATEDIFF(day, 日期1, 日期2)计算天数差，ABS()取绝对值得到“最近”的判断依据。
• 窗口函数标记：PARTITION BY按ID和捐赠日期分组，ORDER BY按日期差升序排列，ROW_NUMBER()为每组内的记录标记序号，序号1即为当前捐赠日期对应的最近费用记录。
• 筛选结果：外层查询通过JOIN关联子查询结果，筛选rn=1的记录得到最终匹配数据。

简化版本（SQL Server 2012+适用） #

如果你的SQL Server版本支持CROSS APPLY，可以用更简洁的写法：

SELECT 
    a.ID_A,
    a.ID_B,
    a.Donation_date,
    a.Donation_amount,
    b.Expense_date AS Ref_Expense_date,
    b.Expense_amount AS Ref_expense_amount
FROM TableA a
CROSS APPLY (
    SELECT TOP 1 
        Expense_date,
        Expense_amount
    FROM TableB b
    WHERE b.ID_A = a.ID_A AND b.ID_B = a.ID_B
    ORDER BY ABS(DATEDIFF(day, CONVERT(date, a.Donation_date, 103), CONVERT(date, b.Expense_date, 103))) ASC
) b;

这个写法通过CROSS APPLY为每条TableA记录，从TableB中直接筛选出最近日期的单条费用记录，代码更简洁易读。

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