SQL Server 仅使用最近的值选择不同的行
我有一个包含以下列的表格
I have a table that has the following columns
- 身份证
- 外键 ID
- 属性名称
- 属性值
- 创建
某些数据可能如下所示:
Some of the data may look like this:
1, 1, 'EmailPreference', 'Text', 1/1/2010
2, 1, 'EmailPreference', 'Html', 1/3/2010
3, 1, 'EmailPreference', 'Text', 1/10/2010
4, 2, 'EmailPreference', 'Text', 1/2/2010
5, 2, 'EmailPreference', 'Html', 1/8/2010
我想运行一个查询，为每个不同的 ForeignKeyId 和 AttributeName 提取 AttributeValue 列的最新值，使用 Created 列来确定最新值.示例输出将是:
I'd like to run a query that pulls the most recent value of the AttributeValue column for each distinct ForeignKeyId andAttributeName, using the Created column to determine the most recent value. Example output would be:
ForeignKeyId AttributeName AttributeValue Created
-------------------------------------------------------
1 'EmailPreference' 'Text' 1/10/2010
2 'EmailPreference' 'Html' 1/8/2010
如何使用 SQL Server 2005 执行此操作?
How can I do this using SQL Server 2005?
推荐答案
一种方式
select t1.* from (select ForeignKeyId,AttributeName, max(Created) AS MaxCreated
from YourTable
group by ForeignKeyId,AttributeName) t2
join YourTable t1 on t2.ForeignKeyId = t1.ForeignKeyId
and t2.AttributeName = t1.AttributeName
and t2.MaxCreated = t1.Created
另见 包括聚合列的相关值，用于执行此类查询的 5 种不同方法
See also Including an Aggregated Column's Related Values for 5 different ways to do this kind of query
相关文章