GROUP BY 与 MAX(DATE)

本文介绍了GROUP BY 与 MAX(DATE)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试在表格中列出每列火车的最新目的地(最大出发时间)，forz/a>:

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 not a GROUP BY expression"错误，提示我必须在 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