在where子句中使用select max()函数，优化Sql查询

Question

此查询的目的是返回在售商品及其价格，并且价格应该是最接近但不等于传入日期的日期的价格，基本上是最近可用的价格。并非每天都有价格记录。将聚合选择语句放在where子句中感觉有些不对。是否有更好的方法来做到这一点？也许在连接条件中？

        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已经删除了），包括我的原始建议在内的所有三种方法，在性能方面大致相同。我正试图决定它们中是否有一种以其他难以捉摸的方式提供了额外的好处，例如维护、自说明等，因为这将由其他人来维护。再次感谢。

- Gratzy

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的查询将与 @Remus Rusanu的派生表版本 有相同的查询计划

然而，如果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'

- KM.

---

我认为这可能会奏效，虽然我会限制CTE以过滤符合“打折”条件的产品，但这似乎基本上是相同的事情。使用CTE与子查询相比，性能是否有所不同？因为ProductPrice是一个大表。 - Gratzy

我的回复基本上和你的一样，所以我把自己的删除了。 - Remus Rusanu

Answer 2

这是窗口函数的工作吗？

    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

按照ID编辑分区（假设您的主键是最快的），删除价格分区

- Damon

---

我曾考虑过这样的解决方案，但最终选择了CTE。我认为RANK()应该是：_RANK() OVER(PARTITION BY p.Productid ORDER BY p.Productid,pp.Date DESC) as row_rank_。 - KM.

你说得对，我在RANK()函数上搞错了，它只需要按产品ID进行分区，但是排序不需要，我想。已编辑。 - Damon

Answer 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

- atfergs

---

抱歉，将会返回多个产品，所有产品都在促销中，因此仅使用top 1将只带来第一个产品的第一条记录。 - Gratzy