问题描述
我正在尝试在表格中列出每列火车的最新目的地(最长出发时间)，例如:
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
期望的结果应该是:
Train Dest Time
1 SZ 14:00
2 HK 13:00
我尝试过使用
SELECT Train, Dest, MAX(Time)
FROM TrainTable
GROUP BY Train
我收到ora-00979 不是 GROUP BY 表达式"错误，提示我必须在 group by 语句中包含Dest".但这肯定不是我想要的……
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...
是否可以在一行 SQL 中完成?
Is it possible to do it in one line of SQL?
推荐答案
您不能在结果集中包含未分组的非聚合列.如果一列火车只有一个目的地，那么只需将目的地列添加到您的 group by 子句中，否则您需要重新考虑您的查询.
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.
试试:
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
这篇关于GROUP BY with MAX(DATE)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！