从历史记录表中选择最新状态

SQL Server 2005(以后版本):

WITH MostRecentRows AS
(
    SELECT ID, Name, Data,
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TimeStamp DESC) AS 'RowNumber'
    FROM MySchema.MyTable
) 
SELECT * FROM MostRecentRows 
WHERE RowNumber = 1

假设每个名称都没有重复的时间戳,这样的方法应该可以工作:

SELECT ID, Name, Timestamp, Data
FROM test AS o
WHERE o.Timestamp = (SELECT MAX(Timestamp)
                     FROM test as i
                     WHERE i.name = o.name)

SQL Server 2000:

SELECT
  ID, Name, Timestamp, Data
FROM
  DataTable
  INNER JOIN
  (
     SELECT ID, MAX(Timestamp) Timestamp FROM DataTable GROUP BY ID
  ) latest ON 
    DataTable.ID = Latest.ID AND 
    DataTable.Timestamp = Latest.Timestamp

另一个简单的方法:

SELECT ID,Name,Timestamp, Data
FROM   Test_Most_Recent
WHERE Timestamp = (SELECT MAX(Timestamp)
                 FROM Test_Most_Recent
                 group by Name);

如果您使用的是SQL Server 2005/2008,那么Mitch Weat列出的CTE解决方案从性能角度来说是最好的。但是,如果您使用的是SQL Server 2000,则不能假定没有重复的名称时间戳组合。使用以下代码为每个名称仅返回一条记录:

SELECT ID
    , Name
    , TimeStamp
    , Data
FROM DataTable dt
INNER JOIN
    (SELECT Name
    , MIN(DataTable.ID) AS MinimumID
FROM DataTable  
INNER JOIN  
    (SELECT Name
        , MAX(Timestamp) AS Timestamp 
    FROM DataTable 
    GROUP BY Name) latest 
    ON DataTable.Name = Latest.Name
    AND DataTable.Timestamp = Latest.Timestamp
GROUP BY Name) MinimumLatest
ON dt.ID = MinimumLatest.ID

因此,如果您添加另一个记录,如9c 30,那么这将只返回id 6。如果你不走这么远,那么你可能会返回9 C 30和6 C 30。