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

The purpose of this query is to bring back products and their prices for products on sale and the price should be from the date closest but not equal to the date passed in, essentially the most recent price available. There are not price records for every day. Something feels a little wrong about having the aggregate select statement in the where clause. Is there a better way to do this? Maybe in the join criteria?

此查询的目的是将产品和产品的价格和价格从最接近的日期，但不等于通过的日期，本质上是最近的价格。每天都没有价格记录。在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'

The actually query is a little more complicated but this is essentially the problem. Thanks for your input.

实际上查询有点复杂，但本质上这就是问题所在。谢谢你的输入。

EDIT There will be more than one product returned

编辑将返回不止一个产品

EDIT I'm experimenting with both @Remus Rusanu's and @km's suggestions (although @Remus Rusanu removed his) all three, including my original, seem to be about the same in terms of performance. I'm trying to decide if one offers a benefit over the others in some other intangible way i.e. maintenance, self documenting etc. as this will be maintained by someone else. Thanks again.

编辑:我正在试验@Remus Rusanu和@km的建议(尽管@Remus Rusanu删除了他的建议)三个，包括我的原作，在性能上似乎都差不多。我试着决定一个人是否以其他无形的方式提供利益，比如维护，自我记录等等，因为这将由其他人来维护。再次感谢。

3 个解决方案

#1

try this:

试试这个:

;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'

EDIT based on OP's comment:

基于OP的评论编辑:

I think the above query with CTE will have the same query plan as the the derived table version from @Remus Rusanu

我认为上述使用CTE的查询将与来自@Remus Rusanu的派生表版本具有相同的查询计划

However, if the productprice table is large, you may want to reduce it by filtering by the "OnSale" like here:

但是，如果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'

#2

Is this a job for window functions?

这是窗口函数的工作吗?

    SELECT * FROM (select  
            p.ProductName,
            pp.Price,
            pp.Date,
            RANK() OVER(PARTITION BY p.ProductId ORDER BY pp.Date DESC) as row_rank
            from product p
              join productprice pp  on p.productid = pp.productid
            where 
              pp.date < @DateIn
              and p.producttype = 'OnSale'
    ) saleprice
    where row_rank = 1

EDIT partitions by id (assuming your primary key is fastest), partiton on price removed

根据id(假设主键是最快的)编辑分区，去掉价格部分

#3

SELECT TOP 1 p.ProductName, pp.Price, pp.Date,
FROM product p
INNER JOIN productprice pp on ...
WHERE pp.date < @DateIn
ORDER BY pp.date DESC

#1