按最大日期分组

Question

我正在尝试在表格中列出每个火车最新的目的地（最大出发时间），例如：

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”。但这肯定不是我想要的...

有没有可能在一行SQL语句中完成？

- Aries

---

对于那些想知道的人，最干净的“纯SQL”解决方案是[Joe的方案]（https://dev59.com/sXA75IYBdhLWcg3wFEoI#14841015）。第二名是[Claudio的方案]（https://dev59.com/sXA75IYBdhLWcg3wFEoI#24370489）。 - Pacerier

Answer 1

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

- Thilo

---

@Aries - Thilo的答案比Oliver的更优，因为Thilo的答案往往会执行较少的I/O。分析函数允许SQL在单个遍历中处理表格，而Oliver的解决方案需要多次遍历。 - Adam Musch

同意，GROUP BY 会导致不必要的性能损失。使用这种方法或者甚至是 Left Join 会更加高效，特别是对于大表格来说。 - Joe Meyer

上面的代码和下面使用row_number的代码有什么区别？有人能解释一下吗？ SELECT train, dest, time FROM ( SELECT train, dest, time, ROW_NUMBER() OVER (PARTITION BY train ORDER BY time DESC) rn FROM traintable ) where rn = 1 - Bat_Programmer

@Ruslan，是的，我很同情MySQL开发人员。 - Pacerier

这对我有用，但是我必须给表取一个别名，并在实际使用之前将其用作“dest_rank”的前缀。例如，选择From（blah）作为t，其中t.dest_rank = 1。除此之外，非常好的答案！ - Urk

Answer 2

如果结果集中有未分组的非聚合列，则无法包含这些列。如果一列火车只有一个目的地，那么只需将目的地列添加到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

- Oliver Hanappi

---

注意，如果最大值(time)存在“并列”的情况，这个方法将无法正常工作，因为你会得到多行数据。你需要在上面整个查询语句的后面添加 group by train,dest 来解决这个问题。 - Pacerier

好的。但是如果内部查询与另一个表连接，这将如何工作呢？假设TrainTable中的Destination有自己的表。因此，内部查询将如下所示：select t.train, d.dest, max(time) from TrainTable t join Destination d on t.destid = d.id group by t.train, d.dest - not_ur_avg_cookie

Answer 3

这里有一个只使用左连接的示例，我相信它比任何分组方法都更有效：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;

- Joe Meyer

---

我喜欢这种方法，因为它仅使用标准 SQL，而且非常好用和快速。 - GreenTurtle

许多人不断称赞这个解决方案是“出色的”和“最好的”，然而没有一个人尝试过它。简而言之，它不起作用。正确的查询语句是：select t1.* from TrainTable t1 left join TrainTable t2 on (t1.Train= t2.Train and t1.Time < t2.Time) where t2.Time is null。 - Pacerier

@Pacerier 你说得对，从期望的结果来看，那确实是正确的 SQL（尽管概念相同）。我已经更新了我的答案。 - Joe Meyer

有趣，但我在我的MS SQL Server服务器上进行了检查（21000条记录），发现这比MAX + GROUP BY慢3倍。 - CoperNick

非常慢！带有日期、从哪里、到哪里和汇率字段的货币转换表，共203161行。Joe Meyer方法返回了362行结果（用时31.29秒）。Oliver Hanappi方法返回了362行结果（用时0.04秒）。 - TheRoSS

Answer 4

另一种解决方案：

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

- Claudio Negri

---

注意，如果最大时间存在“并列”的情况，那么这段代码将无法正常工作，因为会返回多行记录。请改用以下代码：select * from traintable where (train, time) in (select train, max(time) from traintable group by train) group by train,dest; - Pacerier

Answer 5

只要没有重复的情况发生（而火车通常只到达一个站台），……

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

- Gary Myers

---

这并没有被说明。 - Pacerier

Answer 6

我知道我来晚了，但试试这个...

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

来源: Group Concat 文档

编辑: 修正了 SQL 语法

需要翻译的内容涉及 IT 技术，敬请期待。

- Gravy

---

文档链接已损坏。请尝试此链接。 - f.llanquipichun