如何为Transactions表每条记录匹配Premium表最近符合条件的费率

以下方案均可以实现匹配规则，可根据你使用的数据库类型选择： #

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

兼容MySQL、PostgreSQL、SQL Server等所有主流关系型数据库：

SELECT 
  t.tx_id,
  t.cost,
  (
    SELECT p.rate 
    FROM Premium p 
    WHERE p.since_tx_id < t.tx_id 
    ORDER BY p.since_tx_id DESC 
    LIMIT 1
  ) AS rate
FROM Transactions t

如果使用的数据库不支持LIMIT语法（如低版本SQL Server），可改用先取最大since_tx_id再关联的写法：

SELECT 
  t.tx_id,
  t.cost,
  p.rate
FROM Transactions t
LEFT JOIN (
  SELECT 
    t_inner.tx_id,
    MAX(p_inner.since_tx_id) AS max_since_id
  FROM Transactions t_inner
  INNER JOIN Premium p_inner 
    ON p_inner.since_tx_id < t_inner.tx_id
  GROUP BY t_inner.tx_id
) t_max ON t.tx_id = t_max.tx_id
LEFT JOIN Premium p 
  ON p.since_tx_id = t_max.max_since_id

方案2：LATERAL JOIN（高性能，适合大数据量） #

支持PostgreSQL、Spark SQL、BigQuery、SQL Server 2005及以上版本，性能优于子查询：

SELECT 
  t.tx_id,
  t.cost,
  p.rate
FROM Transactions t
LEFT JOIN LATERAL (
  SELECT rate 
  FROM Premium 
  WHERE since_tx_id < t.tx_id 
  ORDER BY since_tx_id DESC 
  LIMIT 1
) p ON TRUE

方案3：窗口函数实现 #

适合支持RANGE BETWEEN语法的数据库，两张表数据量都很大时效率更高：

WITH all_data AS (
  SELECT tx_id, cost, NULL AS rate FROM Transactions
  UNION ALL
  SELECT since_tx_id AS tx_id, NULL AS cost, rate FROM Premium
),
fill_rate AS (
  SELECT 
    tx_id,
    cost,
    MAX(rate) OVER (ORDER BY tx_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS rate
  FROM all_data
)
SELECT tx_id, cost, rate 
FROM fill_rate 
WHERE cost IS NOT NULL
ORDER BY tx_id DESC

如果你之前用ROW_NUMBER或者MAX没有得到正确结果，通常是因为没有先按每个tx_id匹配出对应的最大since_tx_id，直接聚合导致rate和since_tx_id的对应关系错位。

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