如何使用SQL组筛选具有最大日期值的行 [英] how to use SQL group to filter rows with maximum date value
问题描述
我有以下表
CREATE TABLE测试
(`Id` INT,`value` VARCHAR( 20),`adate` varchar(20))
;
INSERT INTO测试
(`Id`,`value`,`adate`)
值
(1,100, '2014年1月1日') ,
(1,200,'2014-01-02'),
(1,300,'2014-01-03'),
(2,200,'2014-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的结果。即
Id,value,adate
1,300,' 2014-01-03'
2,30,'2014-01-04'
3,60,'2014-01-04'
如何使用 group by
来实现这一点?我做了如下,但它不工作。
选择Id,value,adate
from Test b $ b group by Id,value,adate
有adate = MAX(adate)
有人可以帮助查询吗?
如果您使用的是具有分析功能的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;
I have the following table
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')
;
I want to achieve the result which selects only Id having max value of date. ie
Id ,value ,adate
1, 300,'2014-01-03'
2, 30 ,'2014-01-04'
3, 60 ,'2014-01-04'
how can I achieve this using group by
? I have done as follows but it is not working.
Select Id,value,adate
from Test
group by Id,value,adate
having adate = MAX(adate)
Can someone help with the query?
If you are using a DBMS that has analytical functions you can use 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;
Otherwise you will need to use a join to the aggregated max date by Id to filter the results from Test
to only those where the date matches the maximum date for that 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;
这篇关于如何使用SQL组筛选具有最大日期值的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!