SQL Server 2014中如何匹配行程日期对应的生效费率

你遇到的核心问题是：普通JOIN后的子查询无法引用外部表的SomeTrip.TripDate字段，而SQL Server提供了两种常用的方法来解决这个"逐行匹配最新记录"的需求——使用OUTER APPLY或者窗口函数ROW_NUMBER()结合分区。

方法一：使用OUTER APPLY（推荐，更高效直观） #

OUTER APPLY允许内部子查询直接访问外部表的列，并且会为外部表的每一行执行一次子查询，正好符合我们需要为每个TripDate单独查找最新费率的场景：

SELECT 
    st.ID AS TripID,
    st.TripDate,
    sr.ID AS RateID,
    sr.EffectiveDate,
    sr.Rate1,
    sr.Rate2,
    sr.Rate3,
    sr.Rate4
FROM SomeTrip st
OUTER APPLY (
    -- 为当前行程的TripDate筛选出最新的生效费率
    SELECT TOP 1 *
    FROM SomeRates sr
    WHERE sr.EffectiveDate <= st.TripDate
    ORDER BY sr.EffectiveDate DESC
) sr;

这个查询会为SomeTrip中的每一行，执行一次内部子查询，找到EffectiveDate小于等于当前TripDate的最新费率记录，最终返回你期望的结果。如果某个行程没有匹配的费率（比如TripDate早于所有EffectiveDate），OUTER APPLY会返回NULL，和你原来的LEFT OUTER JOIN行为一致。

方法二：使用窗口函数ROW_NUMBER() #

如果你更习惯用窗口函数的方式，可以先关联所有符合条件的费率记录，再通过分区筛选出每个行程对应的最新费率：

WITH TripRateMatches AS (
    SELECT 
        st.ID AS TripID,
        st.TripDate,
        sr.ID AS RateID,
        sr.EffectiveDate,
        sr.Rate1,
        sr.Rate2,
        sr.Rate3,
        sr.Rate4,
        -- 按行程ID分区，对每个行程的关联费率按生效日期降序编号
        ROW_NUMBER() OVER (PARTITION BY st.ID ORDER BY sr.EffectiveDate DESC) AS rn
    FROM SomeTrip st
    LEFT JOIN SomeRates sr ON sr.EffectiveDate <= st.TripDate
)
-- 取每个分区中编号为1的记录（即最新的费率）
SELECT 
    TripID,
    TripDate,
    RateID,
    EffectiveDate,
    Rate1,
    Rate2,
    Rate3,
    Rate4
FROM TripRateMatches
WHERE rn = 1;

这个方法的逻辑是：先把所有行程和所有生效日期早于等于它的费率记录关联起来，然后用PARTITION BY st.ID把数据按行程分组，每个组内按EffectiveDate降序排序，取排序后的第一条记录（rn=1），就是该行程对应的最新费率。

两种方法都能得到你想要的结果，其中OUTER APPLY在数据量较大时通常性能更优，因为它避免了先做全量关联再筛选的步骤。

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