使用 where 子句中的 select max() 函数改进 Sql 查询 [英] Improve Sql Query with select max() function in where clause

问题描述

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

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?

        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.

编辑将有不止一件产品退回

编辑我正在试验@Remus Rusanu 和@km 的建议(尽管@Remus Rusanu 删除了他的)所有三个，包括我的原始建议，在性能方面似乎大致相同.我试图确定一个人是否以某种其他无形的方式比其他人提供好处，即维护、自我记录等，因为这将由其他人维护.再次感谢.

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.

推荐答案

试试这个:

;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 的派生表版本

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

但是，如果 productprice 表很大，您可能希望通过OnSale"过滤来减少它，如下所示:

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

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

这篇关于使用 where 子句中的 select max() 函数改进 Sql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！