如何通过SQL获取销售日期的最后已知价格并计算月度销售额？

我有两张表ProductSales（销售记录）和ProductPrice（产品价格），结构及数据如下：

ProductSales表 #

ProductPrice表 #

原本使用以下SQL计算月度销售额，但当销售日期无对应价格记录时，isnull(Price,0)会导致销售额计算错误，需要修改为取销售日期之前最近的已知价格计算：

select SaleProduct, sum(SaleQuantity*isnull(Price,0)) as MonthlySales 
from ProductSales s
left outer join ProductPrice p
    on s.SaleDate = p.PriceDate and s.SaleProduct = p.Product
where saledate between '2023-10-01' and '2023-10-31'
group by SaleProduct

解决方案1：使用OUTER APPLY（适用于SQL Server） #

通过OUTER APPLY为每条销售记录匹配最近的价格记录，核心逻辑是找到PriceDate <= SaleDate的最大价格日期，再关联获取对应价格：

select 
    s.SaleProduct,
    sum(s.SaleQuantity * isnull(p.Price, 0)) as MonthlySales
from ProductSales s
outer apply (
    select top 1 Price
    from ProductPrice
    where 
        lower(Product) = lower(s.SaleProduct) -- 统一大小写避免匹配失败
        and PriceDate <= s.SaleDate
    order by PriceDate desc -- 取最近的价格日期
) p
where s.saledate between '2023-10-01' and '2023-10-31'
group by s.SaleProduct

解决方案2：使用窗口函数（通用多数数据库） #

先为每个产品的价格记录按日期排序，再通过ROW_NUMBER()为每条销售记录匹配最近的价格：

with RankedPrices as (
    select 
        Product,
        PriceDate,
        Price,
        row_number() over (partition by lower(Product) order by PriceDate desc) as rn
    from ProductPrice
)
select 
    s.SaleProduct,
    sum(s.SaleQuantity * isnull(rp.Price, 0)) as MonthlySales
from ProductSales s
left join RankedPrices rp
    on lower(rp.Product) = lower(s.SaleProduct)
    and rp.PriceDate <= s.SaleDate
    and rp.rn = 1 -- 锁定最近的价格记录
where s.saledate between '2023-10-01' and '2023-10-31'
group by s.SaleProduct

关键说明 #

• 大小写兼容：ProductPrice表存在Product2和product2这类大小写不一致的记录，用lower()统一转换后匹配，避免漏匹配。
• 最近价格逻辑：通过order by PriceDate desc或窗口函数排序，确保取到销售日期之前最新的有效价格。
• 空值处理：保留isnull(Price,0)，若某产品完全无价格记录，销售额按0计算（可根据需求调整为忽略此类记录）。

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