SQL如何关联订单与汇率表按对应日期计算英镑价格

核心逻辑是给每个订单匹配所有更新日期早于等于订单购买日期的汇率，再从中筛选出更新日期最大的那一条对应的汇率，再计算对应英镑金额即可，以下是三种不同场景的实现方案：

方案1：关联子查询（全数据库兼容） #

无需数据库支持高级语法，所有主流SQL环境都可直接运行：

SELECT
  o.order_id,
  o.dollar_amount AS dollar_price,
  r.dollar_to_pound AS pound_value,
  o.dollar_amount * r.dollar_to_pound AS calculated
FROM Orders o
LEFT JOIN 汇率表名 r 
  ON r.update_date = (
    SELECT MAX(update_date) 
    FROM 汇率表名 
    WHERE update_date <= o.purchase_date
  )
ORDER BY o.order_id;

注：请将SQL中的汇率表名替换为你实际存储汇率的表名。

方案2：窗口函数预生成生效区间（大数据量性能更优） #

如果你的数据库支持窗口函数（MySQL8.0+、PostgreSQL、SQL Server、Oracle等均支持），可以先预计算每个汇率的生效时间范围，再做范围关联，性能远高于逐行子查询：

WITH RateValidPeriod AS (
  SELECT
    dollar_to_pound,
    update_date AS valid_start,
    -- 下一个汇率的更新日期即为当前汇率的失效日期
    LEAD(update_date, 1, '9999-12-31') OVER (ORDER BY update_date) AS valid_end
  FROM 汇率表名
)
SELECT
  o.order_id,
  o.dollar_amount AS dollar_price,
  r.dollar_to_pound AS pound_value,
  o.dollar_amount * r.dollar_to_pound AS calculated
FROM Orders o
LEFT JOIN RateValidPeriod r
  ON o.purchase_date >= r.valid_start AND o.purchase_date < r.valid_end
ORDER BY o.order_id;

方案3：横向连接（语法最直观） #

支持LATERAL JOIN特性的数据库可以直接用如下写法，逻辑最清晰：

SELECT
  o.order_id,
  o.dollar_amount AS dollar_price,
  r.dollar_to_pound AS pound_value,
  o.dollar_amount * r.dollar_to_pound AS calculated
FROM Orders o
LEFT JOIN LATERAL (
  SELECT dollar_to_pound
  FROM 汇率表名
  WHERE update_date <= o.purchase_date
  ORDER BY update_date DESC
  LIMIT 1
) r ON TRUE
ORDER BY o.order_id;

以上三种方案运行后都可以得到你给出的预期结果。

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