如何使用 SQL 组过滤具有最大日期值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24140048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to use SQL group to filter rows with maximum date value
提问by NewtonCode
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 的结果。IE
Id ,value ,adate
ID，值，日期
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.
我怎样才能做到这一点group by？我做了如下，但它不工作。
Select Id,value,adate
from Test
group by Id,value,adate
having adate = MAX(adate)
Can someone help with the query?
有人可以帮忙查询吗？
采纳答案by GarethD
If you are using a DBMS that has analytical functions you can use ROW_NUMBER:
如果您使用具有分析功能的 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;
Otherwise you will need to use a join to the aggregated max date by Id to filter the results from Testto only those where the date matches the maximum date for that Id
否则，您将需要通过 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;
回答by Mike Sherrill 'Cat Recall'
Select the maximum dates for each id.
选择每个 ID 的最大日期。
select id, max(adate) max_date
from test
group by id
Join on that to get the rest of the columns.
加入它以获得其余的列。
select t1.*
from test t1
inner join (select id, max(adate) max_date
from test
group by id) t2
on t1.id = t2.id and t1.adate = t2.max_date;
回答by TechDo
Please try:
请尝试：
select
*
from
tbl a
where
a.adate=(select MAX(adate) from tbl b where b.Id=a.Id)
回答by TechDo
I would try something like this
我会尝试这样的事情
Select t1.Id, t1.value, t1.adate
from Test as t1
where t1.adate = (select max(t2.adate)
from Test as t2
where t2.id = t1.id)
