GROUP BY with MAX(DATE)

我试图在表例如中列出每列火车的最新目的地(最大出发时间):

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 not a GROUP by expression”错误，说我必须在我的GROUP by语句中包含“Dest”。但这肯定不是我想要的……

有可能在一行SQL中完成吗?

464685 次浏览

小开

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子句中，否则需要重新考虑查询。

试一试:

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

小开

只要没有重复的(火车往往一次只到达一个站)……

select Train, MAX(Time),
max(Dest) keep (DENSE_RANK LAST ORDER BY Time) max_keep
from TrainTable
GROUP BY Train;

小开

下面是一个只使用左连接的例子，我相信它比任何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;

小开

另一个解决方案:

select * from traintable
where (train, time) in (select train, max(time) from traintable group by train);

小开

我知道我迟到了，但是试试这个…

SELECT
`Train`,
`Dest`,
SUBSTRING_INDEX(GROUP_CONCAT(`Time` ORDER BY `Time` DESC), ",", 1) AS `Time`
FROM TrainTable
GROUP BY Train;

Src: 组Concat文件

编辑:修正sql语法