如何使用SQL跨表关联实现按日期匹配规则的货币金额转换

核心匹配规则梳理 #

先明确需求里的汇率映射逻辑，避免关联出错：

• 交易发生在每年1月：匹配上一年度12月的累计汇率
• 交易发生在每年N月（N≥2）：匹配同一年度N-1月的累计汇率
• 无对应货币代码映射、无匹配汇率的交易，直接保留原始金额作为转换后金额

表关联逻辑说明 #

三张表全部采用左连接关联，保证所有交易记录不会因为匹配不到映射/汇率被过滤：

1. 交易金额表作为主表，左连货币代码映射表，通过货币名称匹配拿到标准货币代码
2. 基于交易日期计算每条记录需要匹配的汇率年份、对应月度，再左连汇率转换表
3. 用COALESCE函数处理空值，匹配不到汇率时直接返回原始金额

可直接复用的SQL代码 #

以下为ANSI标准SQL写法，适配绝大多数主流数据库，若使用的数据库日期函数有差异（比如部分数据库取年月用YEAR()/MONTH()而非EXTRACT），可按需微调：

WITH trade_with_match_param AS (
    SELECT
        t.Currency,
        t.Date,
        t.Amount,
        m.CUR,
        -- 计算要匹配的汇率年份
        CASE
            WHEN EXTRACT(MONTH FROM t.Date) = 1 THEN EXTRACT(YEAR FROM t.Date) - 1
            ELSE EXTRACT(YEAR FROM t.Date)
        END AS match_year,
        -- 计算要匹配的汇率月份
        CASE
            WHEN EXTRACT(MONTH FROM t.Date) = 1 THEN 12
            ELSE EXTRACT(MONTH FROM t.Date) - 1
        END AS match_month
    FROM 交易金额表 t
    LEFT JOIN 货币代码映射表 m
        ON t.Currency = m.Currency
)
SELECT
    twmp.Currency,
    twmp.Date,
    twmp.Amount,
    twmp.CUR,
    -- 匹配不到汇率时直接取原始金额
    COALESCE(
        twmp.Amount * CASE twmp.match_month
            WHEN 1 THEN r.YTDJAN
            WHEN 2 THEN r.YTDFeb
            -- 后续月份按汇率表实际字段名往下补充即可，比如3月对应YTDMAR，12月对应YTDDEC
            WHEN 12 THEN r.YTDDEC
        END,
        twmp.Amount
    ) AS Converted
FROM trade_with_match_param twmp
LEFT JOIN 汇率转换表 r
    ON twmp.CUR = r.CUR
    AND twmp.match_year = r.`Currency Year`

逻辑校验说明 #

• 2022年2月的交易：计算得到match_year=2022、match_month=1，关联2022年汇率记录取YTDJAN字段，和需求示例一致
• 2020年1月的交易：计算得到match_year=2019、match_month=12，关联2019年汇率记录取12月对应汇率字段，和需求示例一致
• 欧元这类无映射的交易：CUR字段为null，关联不到汇率表，COALESCE函数直接返回原始金额，符合需求要求

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