SQL查询以获取最新价格 [英] SQL Query to get latest price

问题描述

我有一个表，其中包含 MS SQL 2005 表中许多不同事物"的价格.每件物品每天有数百条记录，不同的物品会在不同时间获得价格更新.

I have a table containing prices for a lot of different "things" in a MS SQL 2005 table. There are hundreds of records per thing per day and the different things gets price updates at different times.

ID uniqueidentifier not null,
ThingID int NOT NULL,
PriceDateTime datetime NOT NULL,
Price decimal(18,4) NOT NULL

我需要获取一组商品今天的最新价格.下面的查询有效，但我得到了数百行，我必须循环遍历它们，并且每个 ThingID 只提取最新的一行.我如何(例如通过 GROUP BY)说我想要每个 ThingID 的最新一个?还是我必须使用子查询?

I need to get today's latest prices for a group of things. The below query works but I'm getting hundreds of rows back and I have to loop trough them and only extract the latest one per ThingID. How can I (e.g. via a GROUP BY) say that I want the latest one per ThingID? Or will I have to use subqueries?

SELECT * 
FROM Thing
WHERE ThingID IN (1,2,3,4,5,6)
  AND PriceDate > cast( convert(varchar(20), getdate(), 106) as DateTime) 

更新:为了隐藏复杂性，我将 ID 列放在一个 int 中.在现实生活中，它是 GUID(而不是顺序类型).我已经更新了上面的表 def 以使用 uniqueidentifier.

UPDATE: In an attempt to hide complexity I put the ID column in a an int. In real life it is GUID (and not the sequential kind). I have updated the table def above to use uniqueidentifier.

推荐答案

我认为您的表结构的唯一解决方案是使用子查询:

I think the only solution with your table structure is to work with a subquery:

SELECT *
   FROM Thing
   WHERE ID IN (SELECT max(ID) FROM Thing 
                   WHERE ThingID IN (1,2,3,4)
                   GROUP BY ThingID)

(给定最高的 ID 也意味着最新的价格)

(Given the highest ID also means the newest price)

但是，我建议您添加一个IsCurrent"列，如果不是最新价格，则为 0，如果是最新价格，则为 1.这会增加数据不一致的可能风险，但是当表变大时(如果它在索引中)会大大加快整个过程.那么你需要做的就是......

However I suggest you add a "IsCurrent" column that is 0 if it's not the latest price or 1 if it is the latest. This will add the possible risk of inconsistent data, but it will speed up the whole process a lot when the table gets bigger (if it is in an index). Then all you need to do is to...

SELECT *
   FROM Thing
   WHERE ThingID IN (1,2,3,4)
     AND IsCurrent = 1

更新

好的，Markus 更新了问题以表明 ID 是一个 uniqueid，而不是一个 int.这使得编写查询变得更加复杂.

Okay, Markus updated the question to show that ID is a uniqueid, not an int. That makes writing the query even more complex.

SELECT T.* 
   FROM Thing T
   JOIN (SELECT ThingID, max(PriceDateTime)
            WHERE ThingID IN (1,2,3,4)
            GROUP BY ThingID) X ON X.ThingID = T.ThingID 
                                AND X.PriceDateTime = T.PriceDateTime
   WHERE ThingID IN (1,2,3,4)

我真的建议使用IsCurrent"列或使用答案中的其他建议并使用当前价格"表和单独的价格历史"表(这最终将是最快的，因为它保持价格表本身很小).

I'd really suggest using either a "IsCurrent" column or go with the other suggestion found in the answers and use "current price" table and a separate "price history" table (which would ultimately be the fastest, because it keeps the price table itself small).

(我知道底部的 ThingID 是多余的.试试看有没有WHERE"它会更快.不确定优化器完成工作后哪个版本会更快.)

(I know that the ThingID at the bottom is redundant. Just try if it is faster with or without that "WHERE". Not sure which version will be faster after the optimizer did its work.)

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