SQL GROUP BY 与 MAX(DATE)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3491329/
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
GROUP BY with MAX(DATE)
提问by Aries
I'm trying to list the latest destination (MAX departure time) for each train in a table, for example:
我正在尝试在表格中列出每列火车的最新目的地（最大出发时间），例如：
Train Dest Time
1 HK 10:00
1 SH 12:00
1 SZ 14:00
2 HK 13:00
2 SH 09:00
2 SZ 07:00
The desired result should be:
想要的结果应该是：
Train Dest Time
1 SZ 14:00
2 HK 13:00
I have tried using
我试过使用
SELECT Train, Dest, MAX(Time)
FROM TrainTable
GROUP BY Train
by I got a "ora-00979 not a GROUP BY expression" error saying that I must include 'Dest' in my group by statement. But surely that's not what I want...
我收到了“ora-00979 not a GROUP BY expression”错误，提示我必须在 group by 语句中包含“Dest”。但这肯定不是我想要的......
Is it possible to do it in one line of SQL?
是否可以在一行 SQL 中完成？
回答by Thilo
SELECT train, dest, time FROM (
SELECT train, dest, time,
RANK() OVER (PARTITION BY train ORDER BY time DESC) dest_rank
FROM traintable
) where dest_rank = 1
回答by Oliver Hanappi
You cannot include non-aggregated columns in your result set which are not grouped. If a train has only one destination, then just add the destination column to your group by clause, otherwise you need to rethink your query.
您不能在结果集中包含未分组的非聚合列。如果一列火车只有一个目的地，那么只需将目的地列添加到您的 group by 子句中，否则您需要重新考虑您的查询。
Try:
尝试：
SELECT t.Train, t.Dest, r.MaxTime
FROM (
SELECT Train, MAX(Time) as MaxTime
FROM TrainTable
GROUP BY Train
) r
INNER JOIN TrainTable t
ON t.Train = r.Train AND t.Time = r.MaxTime
回答by Joe Meyer
Here's an example that only uses a Left join and I believe is more efficient than any group by method out there: ExchangeCore Blog
这是一个仅使用 Left join 的示例，我认为它比任何 group by 方法都更有效：ExchangeCore 博客
SELECT t1.*
FROM TrainTable t1 LEFT JOIN TrainTable t2
ON (t1.Train = t2.Train AND t1.Time < t2.Time)
WHERE t2.Time IS NULL;
回答by Claudio Negri
Another solution:
另一种解决方案：
select * from traintable
where (train, time) in (select train, max(time) from traintable group by train);
回答by Gary Myers
As long as there are no duplicates (and trains tend to only arrive at one station at a time)...
只要没有重复（火车往往一次只到达一个车站）......
select Train, MAX(Time),
max(Dest) keep (DENSE_RANK LAST ORDER BY Time) max_keep
from TrainTable
GROUP BY Train;
回答by Gravy
I know I'm late to the party, but try this...
我知道我参加聚会迟到了，但是试试这个...
SELECT
`Train`,
`Dest`,
SUBSTRING_INDEX(GROUP_CONCAT(`Time` ORDER BY `Time` DESC), ",", 1) AS `Time`
FROM TrainTable
GROUP BY Train;
Src: Group Concat Documentation
源代码：Group Concat 文档
Edit: fixed sql syntax
编辑：固定 sql 语法
