SQL Server 仅使用最近的值选择不同的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3442931/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL server select distinct rows using most recent value only
提问by Chris
I have a table that has the following columns
我有一个包含以下列的表
- Id
- ForeignKeyId
- AttributeName
- AttributeValue
- Created
- ID
- 外键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
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 列来确定最新值。示例输出将是：
ForeignKeyId AttributeName AttributeValue Created
-------------------------------------------------------
1 'EmailPreference' 'Text' 1/10/2010
2 'EmailPreference' 'Html' 1/8/2010
How can I do this using SQL Server 2005?
如何使用 SQL Server 2005 执行此操作？
回答by SQLMenace
One way
单程
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
See also Including an Aggregated Column's Related Valuesfor 5 different ways to do this kind of query
另请参阅包括聚合列的相关值以了解执行此类查询的 5 种不同方法
回答by OMG Ponies
Use:
用：
SELECT x.foreignkeyid,
x.attributename,
x.attributevalue,
x.created
FROM (SELECT t.foreignkeyid,
t.attributename,
t.attributevalue,
t.created,
ROW_NUMBER() OVER (PARTITION BY t.foreignkeyid, t.attributename
ORDER BY t.created DESC) AS rank
FROM TABLE t) x
WHERE x.rank = 1
Using a CTE:
使用 CTE：
WITH summary AS (
SELECT t.foreignkeyid,
t.attributename,
t.attributevalue,
t.created,
ROW_NUMBER() OVER (PARTITION BY t.foreignkeyid, t.attributename
ORDER BY t.created DESC) AS rank
FROM TABLE t)
SELECT x.foreignkeyid,
x.attributename,
x.attributevalue,
x.created
FROM summary x
WHERE x.rank = 1
Also:
还：
SELECT t.foreignkeyid,
t.attributename,
t.attributevalue,
t.created
FROM TABLE t
JOIN (SELECT x.foreignkeyid,
x.attributename,
MAX(x.created) AS max_created
FROM TABLE x
GROUP BY x.foreignkeyid, x.attributename) y ON y.foreignkeyid = t.foreignkeyid
AND y.attributename = t.attributename
AND y.max_created = t.created
