sql - 选择 MAX 日期加上 ID 值

Question

请考虑下表...

DECLARE @tmp TABLE
    (
    ID int,
    userID int,
    testID int,
    someDate datetime
    )

...包含以下值:

INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (1, 1, 50, '2010-10-01')
INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (2, 1, 50, '2010-11-01')
INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (3, 1, 50, '2010-12-01')
INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (4, 2, 20, '2010-10-01')
INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (5, 2, 30, '2010-11-01')
INSERT INTO @tmp (ID, userID, testID, someDate) VALUES (6, 2, 20, '2012-11-01')

我需要检索每个 userID/testID 值组合的最大日期，以及随附的 ID 值。结果应该是:

ID   userID  testID  someDate
-------------------------------
3    1       50      2010-12-01
5    2       30      2010-11-01
6    2       20      2012-11-01

当我尝试以下查询时，结果集变得不正确并且显示了所有行。我不能从 GROUP BY 子句中省略 ID，因为它会导致错误。有人可以帮忙吗？将表连接到自身以获取这些值似乎很啰嗦。

SELECT ID, userID, testID, MAX(someDate)
FROM @tmp
GROUP BY testId,userID,ID;

http://www.sqlfiddle.com/#!6/d41d8/5219

Answer 1

请尝试:

select * from (
    select 
        *, 
        ROW_NUMBER() over (partition by userID, testID order by SomeDate desc) Rnum 
    From @tmp
)x where Rnum=1