SQL查询以获取最新价格

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

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

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

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

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”列,或者使用答案中的其他建议,并使用“current price”表和单独的“price history”表(这最终将是最快的,因为它使价格表本身保持较小)。

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

编辑 上面是ANSI SQL,我现在猜子查询中有多个列对T SQL不起作用。马吕斯,我无法测试以下内容,但请尝试;

SELECT
 p.*
FROM
 Thing p,
 (SELECT ThingID, max(PriceDateTime ) FROM Thing WHERE ThingID IN (1,2,3,4) GROUP BY ThingID) m
WHERE 
 p.ThingId = m.ThingId
 and p.PriceDateTime = m.PriceDateTime

如果子查询路径太慢,我会将价格更新视为审核日志,并维护ThingPrice表-可能是价格更新表上的触发器:

ThingID int not null,
UpdateID int not null,
PriceDateTime datetime not null,
Price decimal(18,4) not null

主键应该是ThingID,“UpdateID”是原始表中的“ID”。

由于您使用的是SQL Server 2005,因此可以使用new(CROSS | OUTTER)APPLY子句。APPLY子句让我们用表值函数连接一个表。

为了解决这个问题,首先定义一个表值函数,从Thing中检索特定id的前n行,日期顺序:

CREATE FUNCTION dbo.fn_GetTopThings(@ThingID AS GUID, @n AS INT)
  RETURNS TABLE
AS
RETURN
  SELECT TOP(@n) *
  FROM Things
  WHERE ThingID= @ThingID
  ORDER BY PriceDateTime DESC
GO

然后使用函数检索查询中的前1条记录:

SELECT *
   FROM Thing t
CROSS APPLY dbo.fn_GetTopThings(t.ThingID, 1)
WHERE t.ThingID IN (1,2,3,4,5,6)

这里的神奇之处在于APPLY子句 将函数应用于左侧结果集中的每一行 然后与函数返回的结果集联接,然后重新运行最终结果集。(注意:要执行类似apply的左连接,请使用OUTTER apply,它返回左侧的所有行,而CROSS apply只返回右侧匹配的行)

因为我还不能发表评论(因为报告分数很低),甚至我自己的答案都不能发表评论^^,所以我将在邮件正文中回答: -APPLY子句即使使用表值函数,SQL Server也会对其进行内部优化,使其不会对左侧结果集中的每一行调用函数,而是从函数中获取内部SQL,并将其转换为与查询其余部分一起使用的join子句,因此性能相当甚至更好(如果sql server正确地选择了该计划,并且可以进行进一步优化)而不是使用子查询的查询的性能),并且根据我个人的经验,当数据库正确索引并且统计信息是最新的(就像使用子查询的普通查询在这种情况下的行为一样)时,APPLY没有性能问题

这取决于如何使用数据的性质,但如果旧的价格数据不会像当前的价格数据那样经常使用,这里可能会有一个价格历史记录表的论点。这样,当新价格出现时,非当前数据可能会归档到价格历史记录表(可能是通过触发器)。

正如我所说,根据您的访问模型,这可能是一个选项。

我正在将uniqueidentifier转换为二进制,这样我就可以得到它的最大值。 这应该确保您不会从多个具有相同ThingID和PriceDateTime的记录中获得重复项:

SELECT * FROM Thing WHERE CONVERT(BINARY(16),Thing.ID) IN
(
 SELECT MAX(CONVERT(BINARY(16),Thing.ID))
  FROM Thing
  INNER JOIN
   (SELECT ThingID, MAX(PriceDateTime) LatestPriceDateTime FROM Thing
    WHERE PriceDateTime >= CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
    GROUP BY ThingID) LatestPrices
  ON Thing.ThingID = LatestPrices.ThingID
   AND Thing.PriceDateTime = LatestPrices.LatestPriceDateTime
 GROUP BY Thing.ThingID, Thing.PriceDateTime
) AND Thing.ThingID IN (1,2,3,4,5,6)

由于ID不是连续的,我假设ThingID和PriceDateTime上有一个唯一的索引,因此对于给定的项目,只有一个价格可以是最新的。

此查询将获取列表中的所有项目(如果它们是今天定价的)。如果删除PriceDate的where子句,则无论日期如何,都将获得最新的价格。

SELECT * 
FROM Thing thi
WHERE thi.ThingID IN (1,2,3,4,5,6)
  AND thi.PriceDateTime =
     (SELECT MAX(maxThi.PriceDateTime)
      FROM Thing maxThi
      WHERE maxThi.PriceDateTime >= CAST( CONVERT(varchar(20), GETDATE(), 106) AS DateTime)
        AND maxThi.ThingID = thi.ThingID)

请注意,我将“>”改为“>=”,因为您可以在一天的开始就知道价格

试试这个(如果你只需要最新的 价格 ,而不是该价格的标识符或日期时间)

SELECT ThingID, (SELECT TOP 1 Price FROM Thing WHERE ThingID = T.ThingID ORDER BY PriceDateTime DESC) Price
FROM Thing T
WHERE ThingID IN (1,2,3,4) AND DATEDIFF(D, PriceDateTime, GETDATE()) = 0
GROUP BY ThingID

它必须在不使用全局PK列的情况下工作(例如,对于复杂主键):

SELECT t1.*, t2.PriceDateTime AS bigger FROM Prices t1 
LEFT JOIN Prices t2 ON t1.ThingID = t2.ThingID AND t1.PriceDateTime < t2.PriceDateTime 
HAVING t2.PriceDateTime IS NULL

SELECT ID, ThingID, max(PriceDateTime), Price FROM Thing GROUP BY ThingID