检索每个客户的最新记录 [英] retrieve the most recent record for each customer
本文介绍了检索每个客户的最新记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下数据:
ID NAME DATE
3 JOHN 2011-08-08
2 YOKO 2010-07-07
1 JOHN 2009-06-06
代码(对于SQL Server 2005):
Code (for SQL Server 2005):
DECLARE @TESTABLE TABLE (id int, name char(4), date smalldatetime)
INSERT INTO @TESTABLE VALUES (3, 'JOHN', '2011-08-08')
INSERT INTO @TESTABLE VALUES (2, 'YOKO', '2010-07-07')
INSERT INTO @TESTABLE VALUES (1, 'JOHN', '2009-06-06')
我想为每个名称获取具有最新DATE的ID.像这样:
I want to get, for each NAME, the ID that has the most recent DATE. Like this:
3 JOHN 2011-08-08
2 YOKO 2010-07-07
实现这一目标的最优雅的方法是什么?
What is the most elegant way of accomplishing this?
推荐答案
;WITH x AS
(
SELECT ID, NAME, [DATE],
rn = ROW_NUMBER() OVER
(PARTITION BY NAME ORDER BY [DATE] DESC)
FROM @TESTABLE
)
SELECT ID, NAME, [DATE] FROM x WHERE rn = 1
ORDER BY [DATE] DESC;
尝试避免使用诸如[DATE]
...
Try to avoid reserved words (and vague column names) like [DATE]
...
这篇关于检索每个客户的最新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文