本文介绍了从历史表中选择最近的状态的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
限时送ChatGPT账号..我继承了一个结构如下的表:
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 和6行strong> 应返回,因为它们分别是 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屋!
更多推荐
[db:关键词]
发布评论