如何使用SQL组过滤具有最大日期值的行

Question

我有下表

CREATE TABLE Test
    (`Id` int, `value` varchar(20), `adate` varchar(20))
;

INSERT INTO Test
    (`Id`, `value`, `adate`)
VALUES
    (1, 100, '2014-01-01'),
    (1, 200, '2014-01-02'),
    (1, 300, '2014-01-03'),
    (2, 200, '2014-01-01'),
    (2, 400, '2014-01-02'),
    (2, 30 , '2014-01-04'),
    (3, 800, '2014-01-01'),
    (3, 300, '2014-01-02'),
    (3, 60 , '2014-01-04')
;

我想实现只选择具有日期最大值的ID的结果。IE

id，value，adate

 1, 300,'2014-01-03'     
 2, 30 ,'2014-01-04'     
 3, 60 ,'2014-01-04'

我该如何使用group by呢？我做了如下，但它不起作用。

Select Id,value,adate
from Test
group by Id,value,adate
having adate = MAX(adate)

有人可以协助查询吗？

Answer 1

如果您使用的是具有分析功能的DBMS，则可以使用ROW_NUMBER：

SELECT  Id, Value, ADate
FROM    (   SELECT  ID,
                    Value,
                    ADate,
                    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Adate DESC) AS RowNum
            FROM    Test
        ) AS T
WHERE   RowNum = 1;

否则，您将需要使用按ID联接到聚合的最大日期，以将结果从中筛选出来Test，仅过滤到那些日期与该ID的最大日期相匹配的对象

SELECT  Test.Id, Test.Value, Test.ADate
FROM    Test
        INNER JOIN
        (   SELECT  ID, MAX(ADate) AS ADate
            FROM    Test
            GROUP BY ID
        ) AS MaxT
            ON MaxT.ID = Test.ID
            AND MaxT.ADate = Test.ADate;