基于生效日期查询商品对应价格的SQL语句需求

需求：实现多表关联的SQL查询，根据指定的采购日期（Purchase Date）匹配商品对应的有效价格（Price）。规则为：商品在X日期采购时，显示生效日期不晚于X的最新价格Y。

现有数据表 #

Prices表 #

包含字段：商品编号（Product）、价格（Price）、生效日期（Effective Date）、价格编码（Price code），数据如下：

Purchases表 #

包含字段：商品编号（Product）、采购日期（Purchase Date），数据如下：

期望输出结果 #

问题代码（无法正常运行） #

FROM [dbo.Purchases] a
inner join [dbo].[Prices] b 
on a.[Product] = b.[Product]
   Where [Price code] = 'W'
   and MAX(b.[Effective Date]) <= a.[Purchase Date]

解决方案 #

方法1：窗口函数实现（推荐） #

利用ROW_NUMBER()窗口函数按商品分组，对生效日期倒序排序，筛选出每个采购记录对应的最新有效价格：

SELECT 
    p.Product AS 商品编号,
    p.[Purchase Date] AS 采购日期,
    pr.Price AS 价格
FROM 
    [dbo].[Purchases] p
JOIN (
    SELECT 
        Product,
        Price,
        [Effective Date],
        -- 按商品分组，生效日期倒序排，最新的生效日期标记为1
        ROW_NUMBER() OVER (PARTITION BY Product ORDER BY [Effective Date] DESC) AS rn
    FROM 
        [dbo].[Prices]
    WHERE 
        [Price code] = 'W'
) pr ON p.Product = pr.Product 
    AND pr.[Effective Date] <= p.[Purchase Date]
WHERE 
    pr.rn = 1;

方法2：关联子查询实现 #

通过子查询找到每个商品在采购日期前的最新生效日期，再关联价格表获取对应价格：

SELECT 
    p.Product AS 商品编号,
    p.[Purchase Date] AS 采购日期,
    pr.Price AS 价格
FROM 
    [dbo].[Purchases] p
JOIN [dbo].[Prices] pr 
    ON p.Product = pr.Product
    AND pr.[Effective Date] = (
        -- 子查询获取当前商品、指定价格编码下，不晚于采购日期的最新生效日期
        SELECT MAX([Effective Date])
        FROM [dbo].[Prices]
        WHERE Product = p.Product 
            AND [Price code] = 'W'
            AND [Effective Date] <= p.[Purchase Date]
    )
WHERE 
    pr.[Price code] = 'W';

错误说明 #

原代码无法运行的原因：

• MAX()聚合函数不能直接在WHERE子句中使用，需结合分组或子查询处理
• 未正确针对每个采购记录单独筛选对应商品的最新有效价格，逻辑缺失

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