MySQL如何关联日汇率表换算求和得到以USD计价的总营收

你可以通过订单表左关联汇率表匹配对应日期的汇率后求和，注意要处理无对应汇率的订单场景（你示例中2021-05-01的订单在汇率表中无对应数据），参考SQL如下：

SELECT 
  SUM(o.total_price * COALESCE(er.exchange, 1.18)) AS salesRevenue_usd
FROM Orders o
LEFT JOIN Exchange_rates er 
  ON o.order_date = er.day
  -- 明确指定币种过滤条件，避免同日期多币种汇率数据干扰
  AND er.base_currency = 'EUR' 
  AND er.currency = 'USD';

如果你的业务要求无对应日期汇率时自动取最近的前一日有效汇率，可以用窗口函数实现，参考写法：

WITH order_with_latest_rate AS (
  SELECT 
    o.total_price,
    er.exchange,
    -- 按订单日期倒序排列，取最近的一条有效汇率
    ROW_NUMBER() OVER (PARTITION BY o.id ORDER BY er.day DESC) AS rn
  FROM Orders o
  LEFT JOIN Exchange_rates er
    ON er.day <= o.order_date
    AND er.base_currency = 'EUR' 
    AND er.currency = 'USD'
)
SELECT SUM(total_price * exchange) AS salesRevenue_usd
FROM order_with_latest_rate
WHERE rn = 1;

• 关联汇率表时必须加上base_currency和目标currency的过滤条件，避免同日期存在多组汇率时关联出重复行，导致订单金额重复计算
• 优先使用左连接而非内连接，防止无对应汇率的订单被直接过滤，导致营收统计值偏低
• 必须提前明确汇率缺失的处理规则：可根据业务需求选择固定 fallback 汇率、取最近前一日有效汇率、提前补全汇率表历史全量数据三种方案
• 建议将换算后的本位币金额直接落盘到订单宽表，后续统计无需重复关联汇率表，既提升查询效率，也能避免后续汇率表数据修正导致历史营收统计结果变动
• 多币种业务场景下，建议统一在订单生成时就换算为记账本位币落盘存储，从根源上避免后续统计时的汇率换算一致性问题

内容的提问来源于stack exchange，提问作者Kárpáti András