SQL 查询以获取最新价格，具体取决于日期 [英] SQL query to get latest prices, depending on the date

问题描述

我正在尝试检索已更新的产品列表，该表包含产品的多次更新，因为它记录了价格变化.

I am trying to retrieve a list of products which have been updated, the table contains multiple updates of the products as it records the price changes.

我需要获取所有产品的最新价格变化，但只返回最近的更新.到目前为止，我有以下代码，但它只返回最后一次更新和 1 个产品.

I need to get the latest price changes for all products, but only return the the last update. I have the below code so far, but it only returns the very last update and only 1 product.

SELECT dbo.twProducts.title, dbo.LowestPrices.productAsin, dbo.twProducts.sku, 
       dbo.LowestPrices.tweAmzPrice, dbo.LowestPrices.price, dbo.LowestPrices.priceDate
FROM   dbo.aboProducts INNER JOIN
       dbo.LowestPrices ON dbo.aboProducts.asin = dbo.LowestPrices.productAsin 
       INNER JOIN dbo.twProducts ON dbo.aboProducts.sku = dbo.twProducts.sku
WHERE  (dbo.LowestPrices.priceDate =
        (SELECT MAX(priceDate) AS Expr1
         FROM   dbo.LowestPrices AS LowestPrices_1))

我希望这是有道理的，我不确定我是否以易于理解的方式解释了它.

I hope this makes sense, i am not sure if i have explained it in a way thats easy to understand.

有任何问题欢迎随时提问.

Any questions please feel free to ask.

推荐答案

我认为对您要查找的查询的调整是加入您的子查询，而不仅仅是匹配日期.

I think the adjustment to the query you are looking for is to join your subquery rather than just matching on the Date.

SELECT dbo.twProducts.title, dbo.LowestPrices.productAsin, dbo.twProducts.sku, 
       dbo.LowestPrices.tweAmzPrice, dbo.LowestPrices.price, dbo.LowestPrices.priceDate
FROM   dbo.aboProducts INNER JOIN
       dbo.LowestPrices ON dbo.aboProducts.asin = dbo.LowestPrices.productAsin 
       INNER JOIN dbo.twProducts ON dbo.aboProducts.sku = dbo.twProducts.sku
WHERE  dbo.LowestPrices.priceDate IN
        (SELECT MAX(LowestPrices_1.priceDate)
         FROM   dbo.LowestPrices AS LowestPrices_1 
         WHERE dbo.LowestPrices.productAsin = LowestPrices_1.productAsin)

这将匹配每个产品的 max(priceDate)

This will match on the max(priceDate) for each product

这篇关于SQL 查询以获取最新价格，具体取决于日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！