SQL关联销售与产品费率表BETWEEN条件不满足时取最新记录问题

现有逻辑的问题 #

你的写法存在三个核心问题：

• 关联条件逻辑冗余：你写的OR条件等价于pr.StartDate <= s.TransactionDate，相当于把所有早于等于交易日期的费率全部关联，会出现1行销售匹配多行费率的情况，没有做有效筛选。
• 缺失优先级判断：没有区分「当月匹配」和「历史最新匹配」的优先级，也没有实现「仅保留最优1条匹配」的逻辑，所以结果不符合预期。
• 笔误：表名Product rates被误写为producct rate，会直接导致语法错误。

正确实现方案 #

用窗口函数/OUTER APPLY给匹配结果做优先级排序，取每条销售的最优匹配即可，示例代码（基于SQL Server语法，兼容你用到的eomonth函数）：

SELECT s.Id, s.SurogateKey, s.TransactionDate, pr.Rate, pr.StartDate AS MatchedStartDate
FROM Sales s
OUTER APPLY (
    SELECT TOP 1 pr.*
    FROM [Product rates] pr
    WHERE pr.Id = s.SurogateKey 
      AND pr.StartDate <= s.TransactionDate
    ORDER BY 
        -- 优先匹配当月生效的费率
        CASE WHEN s.TransactionDate BETWEEN pr.StartDate AND EOMONTH(pr.StartDate) THEN 1 ELSE 2 END ASC,
        -- 同优先级下取生效时间最晚的
        pr.StartDate DESC
) pr

逻辑说明 #

OUTER APPLY相当于对每一条销售记录单独检索匹配的费率：

1. 先过滤出所有产品ID匹配、且费率生效时间早等于交易日期的记录
2. 按照「当月匹配优先>生效时间越新越优先」的规则排序
3. 取排序后的第一条作为最终匹配结果，没有匹配记录的字段自动返回NULL，完全符合你提出的规则要求。

结果验证 #

按照你提供的示例数据，运行上述代码会得到如下结果：

注：如果同一个产品同一天存在多条费率记录（如示例中Id=3的产品2020-09-01有两条费率），可以在ORDER BY最后补充额外排序规则（比如pr.Rate DESC）来确定优先取哪一条。

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