如何关联SGD汇率表与支付交易表，匹配对应及最近历史汇率？

表结构与数据 #

SGD汇率表（Table_1） #

支付交易表（Table_2） #

需求说明 #

将支付交易表关联到SGD汇率表，为每笔交易匹配对应货币的汇率：

• 若交易日期存在对应汇率，直接使用该汇率
• 若交易日期无对应汇率，使用该日期之前最新可用的汇率

预期结果 #

尝试的错误SQL #

用户尝试的SQL仅匹配日期完全相等的记录，导致非匹配日期的汇率为NULL：

select Table_2.TRX_DATE, Table_2.CURRENCY, Table_2.AMOUNT, rate
from Table_2
left join Table_1 on Table_2.TRX_DATE >= Table_1.Tgl 
                  and Table_2.TRX_DATE <= Table_1.Tgl 
                  and Table_2.CURRENCY = Table_1.Currency

错误结果 #

正确SQL实现方案 #

方案一：关联子查询（通用型，适用于多数数据库） #

通过子查询为每笔交易筛选同货币下、日期不晚于交易日期的最新汇率：

SELECT 
    t2.TRX_DATE,
    t2.CURRENCY,
    t2.AMOUNT,
    (
        SELECT TOP 1 t1.RATE
        FROM Table_1 t1
        WHERE t1.CURRENCY = t2.CURRENCY
          AND t1.Date <= t2.TRX_DATE
        ORDER BY t1.Date DESC
    ) AS RATE
FROM Table_2 t2;

注：若使用MySQL，需将TOP 1替换为LIMIT 1；若使用PostgreSQL，同样用LIMIT 1。

方案二：窗口函数（适用于支持ROW_NUMBER()的数据库，如SQL Server、MySQL 8+、PostgreSQL等） #

先关联符合日期条件的汇率记录，再通过窗口函数筛选每组的最新汇率：

WITH RankedRates AS (
    SELECT 
        t2.TRX_DATE,
        t2.CURRENCY,
        t2.AMOUNT,
        t1.RATE,
        ROW_NUMBER() OVER (
            PARTITION BY t2.TRX_DATE, t2.CURRENCY 
            ORDER BY t1.Date DESC
        ) AS rn
    FROM Table_2 t2
    LEFT JOIN Table_1 t1 
        ON t1.CURRENCY = t2.CURRENCY
        AND t1.Date <= t2.TRX_DATE
)
SELECT 
    TRX_DATE,
    CURRENCY,
    AMOUNT,
    RATE
FROM RankedRates
WHERE rn = 1;

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