SQL Server：如何获取订单日期前商品的最新默认售价并关联订单？

原查询的核心问题是：子查询仅返回了price_list表中全局最新的一条价格记录，而非每个商品对应订单日期的最新有效价格，导致大部分订单行无法匹配到正确的价格，最终salePrice返回0（或NULL）。

以下两种简便方法可以解决这个问题：

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

通过窗口函数按itemNbr分组，对每个商品的价格按startDate倒序编号，再筛选出每个订单行对应商品中startDate <= orderDate的最新价格：

select
    l.itemNbr, i.itemName, h.orderNbr, h.orderDate, 
    l.Qty, l.price, l.total, sp.salePrice
from
    orderHeader h
join 
    orderLine l on h.id = l.headerId 
join 
    item i on i.itemNbr = l.itemNbr 
left join (
    select 
        itemNbr, salePrice, startDate,
        -- 按商品分组，按生效日期倒序编号
        ROW_NUMBER() OVER(PARTITION BY itemNbr ORDER BY startDate DESC) as rn
    from price_list
) as sp on sp.itemNbr = l.itemNbr 
        and sp.startDate <= h.orderDate
        and sp.rn = 1; -- 取每个商品的最新有效价格

方法2：使用OUTER APPLY（更直观） #

OUTER APPLY可以为每个订单行单独关联符合条件的最新价格记录，逻辑更清晰：

select
    l.itemNbr, i.itemName, h.orderNbr, h.orderDate, 
    l.Qty, l.price, l.total, sp.salePrice
from
    orderHeader h
join 
    orderLine l on h.id = l.headerId 
join 
    item i on i.itemNbr = l.itemNbr 
outer apply (
    -- 对当前订单行的商品，取生效日期<=订单日期的最新价格
    select top 1 salePrice
    from price_list pl
    where pl.itemNbr = l.itemNbr 
      and pl.startDate <= h.orderDate
    order by pl.startDate desc
) as sp;

这两种方法都能准确匹配每个订单行下单时的默认售价，OUTER APPLY的写法更贴近需求逻辑，可读性更强，推荐优先使用。

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