GROUP BY与MAX（日期）

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

我试图列出表格中每列火车的最新目的地（最大出发时间），例如： $ p $ 火车时间点 1 HK 10:00 1 SH 12:00 1 SZ 14:00 2 HK 13:00 2 SH 09:00 2 SZ 07:00 预期结果应该是： pre $ 火车目的地时间 1 SZ 14:00 2 HK 13:00

我曾尝试使用选择Train，Dest，MAX（时间）从TrainTable GROUP BY列车

 
我得到了一个ora-00979不是GROUP BY表达式的错误，说我必须在我的小组中包含'Dest'声明。但肯定这不是我想要的...... 
   
是否可以在一行SQL中执行？
解决方案
不能在未分组的结果集中包含非聚合列。如果一列火车只有一个目的地，那么只需将目标列添加到您的group 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   
 
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...

Is it possible to do it in one line of SQL?
 解决方案 
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.

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