从历史表中选择最近的状态 [英] Select Most Recent States From History Table

问题描述

我继承了一个结构如下的表:

I have inherited a table with a structure something like this:

ID   Name   Timestamp   Data
----------------------------
1    A      40          ...
2    A      30          ...
3    A      20          ...
4    B      40          ...
5    B      20          ...
6    C      30          ...
7    C      20          ...
8    C      10          ...

ID 是身份字段和主键，Name 和 Timestamp 字段上有非唯一索引.

ID is an identity field and the primary key and there are non-unique indexes on the Name and Timestamp fields.

获取每个项目名称的最新记录的最有效方法是什么，即在上面表格中的1、4 和6strong> 应返回，因为它们分别是 A、B 和 C 项的最新条目.

What is the most efficient way to get the most recent record for each item name, i.e. in the table above rows 1,4 and 6 should be returned as they are the most up-to-date entries for items A,B and C respectively.

推荐答案

SQL Server 2005(以后):

SQL Server 2005 (onwards):

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

这篇关于从历史表中选择最近的状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！