使用where子句中的select max（）函数改进Sql查询

Question

此查询的目的是带回产品及其在售产品的价格，价格应从最接近但不等于传入日期的日期开始，本质上是最近的可用价格。没有每天的价格记录。在where子句中使用聚合select语句有些不对劲。有一个更好的方法吗？也许在加入标准中？

        select  
        p.ProductName,
        pp.Price,
        pp.Date,
        from product p
        inner join productprice pp  on p.productid = pp.productid
        where 
        pp.evaluationdate = (select  max(Date) from productprice 
                             where productid = p.productid  
                             and date < @DateIn) 
        and p.producttype = 'OnSale'

实际查询稍微复杂一些，但这本质上是问题。感谢您的输入。

编辑 将返回不止一种产品

编辑 我正在尝试@Remus Rusanu和@km的建议（尽管@Remus
Rusanu删除了他的建议），包括我的原始三个，在性能方面似乎都差不多。我试图确定一个人是否以某种其他无形的方式（例如维护，自我记录等）比其他人受益，因为这将由其他人维护。再次感谢。

Answer 1

试试这个：

;WITH CurrentPrice AS 
(
SELECT productid,max(Date) AS Date
    FROM productprice 
    WHERE date < @DateIn 
    GROUP BY productid
)

select  
    p.ProductName,
    pp.Price,
    pp.Date,
    from product p
        inner join CurrentPrice pa  on p.productid = pa.productid
        inner join productprice pp  on pa.productid = pp.productid AND pa.Date=pp.Date
    where p.producttype = 'OnSale'

*根据OP的评论进行 *编辑 ：

我认为上述带有CTE的查询将具有与@RemusRusanu派生表版本相同的查询计划

但是，如果productprice表很大，您可能希望通过用“ OnSale”进行过滤来减少它，如下所示：

;WITH CurrentPrice AS 
(
select  
    p.productid,
    MAX(pp.Date) AS Date
    from product p
        inner join productprice pp  on pa.productid = pp.productid
    where p.producttype = 'OnSale' AND pp.date < @DateIn 
    GROUP BY productid
)
select  
    p.ProductName,
    pp.Price,
    pp.Date,
    from CurrentPrice           pa
        inner join product      p   on pa.productid = p.productid
        inner join productprice pp  on pa.productid = pp.productid AND pa.Date=pp.Date
    where p.producttype = 'OnSale'