我有此数据:

ID   NAME   DATE
3    JOHN   2011-08-08
2    YOKO   2010-07-07
1    JOHN   2009-06-06

代码(对于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。像这样:
3    JOHN   2011-08-08
2    YOKO   2010-07-07

实现此目的最优雅的方法是什么?

最佳答案

;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]这样的保留字(和模糊的列名)...

关于sql - 检索每个客户的最新记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/7473336/

10-10 14:11
查看更多