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

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

我需要获取一组物品的最新价格。下面的查询有效，但是我要返回几百行，并且必须循环遍历它们，并且每个ThingID仅提取最新的一行。我如何(例如通过GROUP BY)说我想要每个ThingID最新的？还是我必须使用子查询？

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。

解决方案

回答

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

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

(鉴于最高的ID也意味着最新的价格)

但是，我建议我们添加" IsCurrent"列，如果不是最新价格，则该列为0；如果不是最新价格，则为1. 这将增加数据不一致的风险，但是当表变大时(如果它在索引中)，它将大大加快整个过程。然后，我们要做的就是...

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

更新

好的，马库斯(Markus)更新了问题，以显示ID是唯一标识，而不是整数。这使得编写查询变得更加复杂。

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"列，或者与答案中找到的其他建议一起使用，并使用"当前价格"表和单独的"价格历史记录"表(最终会最快，因为它可以保持价格)表本身很小)。

(我知道底部的ThingID是多余的。只需尝试使用或者不使用" WHERE"，它会更快。不确定优化器完成工作后哪个版本会更快。)

回答

我会尝试类似以下子查询的操作，而忘记更改数据结构。

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。 Marius，我无法测试以下内容，请尝试；

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

另一种选择是将日期更改为字符串并与ID串联，这样我们只有一列。但是，这有点令人讨厌。

回答

这取决于数据使用方式的性质，但是如果旧价格数据的使用频率不如当前价格数据那么频繁，则此处可能有一个价格历史记录表的参数。这样，随着新价格的到来，可以将非当前数据归档到价格历史记录表中(可能通过触发器)。

正如我所说，根据访问模型，这可能是一个选择。

回答

如果子查询路线太慢，我将把价格更新视为审核日志，并维护ThingPrice表，也许是价格更新表上的触发器：

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

主键只是ThingID，而" UpdateID"是原始表中的" ID"。

回答

我正在将uniqueidentifier转换为二进制文件，以便获得最大的标识符。
这应该确保我们不会从具有相同ThingID和PriceDateTimes的多个记录中获得重复项：

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)

请注意，我将">"更改为"> ="，因为我们可能会在一天开始时就设置价格

回答

由于使用的是SQL Server 2005，因此可以使用新的(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子句完成的，该子句将函数应用于左侧结果集中的每一行，然后与函数返回的结果集合并，然后重新调整最终结果集。 (注意：要像应用一样进行左联接，请使用OUTTER APPLY从左侧返回所有行，而CROSS APPLY仅返回在右侧具有匹配项的行)

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

回答

尝试此操作(前提是我们只需要最新价格，而不需要该价格的标识符或者日期时间)

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