我试图列出表格中每列火车的最新目的地(最大出发时间),例如: $ 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 $ p
预期结果应该是: 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:00The desired result should be:
Train Dest Time 1 SZ 14:00 2 HK 13:00I have tried using
SELECT Train, Dest, MAX(Time) FROM TrainTable GROUP BY Trainby 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
更多推荐
GROUP BY与MAX(日期)
发布评论