如何使用GROUP BY获取每个组中的最新记录？

Question

假设我有一个名为 messages 的表，它有以下列：

id | from_id | to_id | subject | message | timestamp

我只想获取每个用户的最新消息，就像在点击进入实际聊天界面前在FaceBook收件箱中看到的一样。

这个查询似乎让我接近所需的结果：

SELECT * FROM messages GROUP BY from_id

然而查询结果给出了每个用户最老的消息而不是最新的消息。

我搞不明白这个问题。

- user1019144

---

这个问题甚至有更好的解决方案在这里。 - phil pirozhkov

Answer 1

你应该在每个分组（子查询）中找到最后一个timestamp值，然后将此子查询与表连接 -

SELECT t1.* FROM messages t1
  JOIN (SELECT from_id, MAX(timestamp) timestamp FROM messages GROUP BY from_id) t2
    ON t1.from_id = t2.from_id AND t1.timestamp = t2.timestamp;

- Devart

---

常见问题，但非常感谢您没有使用MySQL允许在SELECT语句中选择不在GROUP BY子句中的列的功能！ - GarethD

非常感谢！我已经成功地添加了另一个表格，没有任何问题。这是一个非常好的方法。 - user1019144

这就是完美的。 - enchance

对于那些不习惯 SQL（像我一样）的人而言，如果我没错的话，'t1'、't2' 和 'timestamp' 都是别名，因此 t2 是内部 SELECT 的结果，而 t2.timestamp 则指向 MAX(timestamp) 列，所以您可以在 ON 语句中从外部 SELECT 中使用它。也许为新手添加一些 "AS" 可以很有帮助 :) - Dani bISHOP

我并不喜欢这个，我真的想要在内部连接中使用自联接而不是隐式子查询；但似乎子查询是唯一的方法。 - ThorSummoner

如果消息表变得很大，这个查询将会很慢。 最好使用文档页面中的方法： http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html - vvolkov

Answer 2

试一试

SELECT * FROM messages where id in (SELECT max(id) FROM messages GROUP BY from_id ) order by id desc

"SELECT max(id) FROM messages GROUP BY from_id" 这个内部查询首先按照用户(from_id)对记录/消息进行分组，然后获取最大的记录id。然后我们再次查询消息表，以仅获取内部查询结果集中的最新记录/消息。

- Venu Morigadi

---

虽然这段代码可以回答问题，但最好加入一些“上下文”来解释它的工作原理和何时使用。仅有代码的回答从长远来看并不有用。 - Benjamin W.

"SELECT max(id) FROM messages GROUP BY from_id" 这个内部查询首先按用户(from_id)分组记录/消息，然后获取最大的记录id。然后我们再次在消息表上查询，以仅从内部查询结果集中获取最新的记录/消息。 - Venu Morigadi

在我看来，最简单的解决方案 - Nowdeen

如果你想根据某个非主键列获取具有最大值的记录，这种方法可能行不通。 - Vivek Giri

这是我唯一的解决办法。 - wdetac

很棒！简单而且功能强大。 - kiradotee

Answer 3

此查询返回每个Form_id的最后一条记录：

    SELECT m1.*
     FROM messages m1 LEFT JOIN messages m2
     ON (m1.Form_id = m2.Form_id AND m1.id < m2.id)
     WHERE m2.id IS NULL;

- leyla azari

---

说实话，这个答案被低估了。这是唯一一个对我有效的解决方案，因为我除了自动递增字段之外还要按不同的字段进行分组，并且必须按日期选择最新的。 - Samuel Willems

这个答案帮助我使用Hibernate HQL使其工作。其他答案都不起作用，因为Hibernate仅支持在WHERE和SELECT之后使用子查询。由于这个答案根本不使用任何子查询，所以它可以正常工作。 - Alex

Answer 4

这是一个标准问题。

需要注意的是，MySQL允许您在GROUP BY子句中省略列，而标准SQL不允许，但通常情况下使用MySQL功能时无法获得确定性结果。

SELECT *
  FROM Messages AS M
  JOIN (SELECT To_ID, From_ID, MAX(TimeStamp) AS Most_Recent
          FROM Messages
         WHERE To_ID = 12345678
         GROUP BY From_ID
       ) AS R
    ON R.To_ID = M.To_ID AND R.From_ID = M.From_ID AND R.Most_Recent = M.TimeStamp
 WHERE M.To_ID = 12345678

我添加了一个筛选器在 To_ID 上，以匹配你可能拥有的内容。如果没有这个条件，查询仍然可以工作，但通常会返回更多的数据。该条件不需要在嵌套查询和外部查询中都声明（优化器应该会自动将条件下推），但像所示一样重复条件也不会有害。

- Jonathan Leffler

---

最新的标准 SQL 允许在 GROUP BY 中省略列，并将它们包含在 SELECT 或 HAVING 子句中，前提是它们在 GROUP BY 组合上功能依赖 - 因此只会返回确定性结果。（当然，MySQL 不检查这种情况。） - ypercubeᵀᴹ

@ypercube 不确定这是否是合适的地方，但你有没有关于这个的好链接。我无法理解如何通过选择不在group by中的列来变得确定，而且还依赖于group by中的项，我唯一看到的使其非确定性的方法是通过使用order by。然而，看到例子可能有助于澄清问题。谢谢 - GarethD

"GROUP BY pk" 是一个简单的例子。我的回答在这里，其中包含（标准的副本）链接：https://dev59.com/0msz5IYBdhLWcg3w47-m#7596265。 - ypercubeᵀᴹ

Answer 5

仅补充Devart所说的，下面的代码并不按照问题所需进行排序：

SELECT t1.* FROM messages t1
  JOIN (SELECT from_id, MAX(timestamp) timestamp FROM messages GROUP BY from_id) t2
    ON t1.from_id = t2.from_id AND t1.timestamp = t2.timestamp;

"GROUP BY" 子句必须在主查询中，因为我们需要首先重新排序 "SOURCE" 以获取所需的 "grouping"。

SELECT t1.* FROM messages t1
  JOIN (SELECT from_id, MAX(timestamp) timestamp FROM messages ORDER BY timestamp DESC) t2
    ON t1.from_id = t2.from_id AND t1.timestamp = t2.timestamp GROUP BY t2.timestamp;

敬礼，

- Bruno de Oliveira

Answer 6

你需要对它们进行排序。

SELECT * FROM messages GROUP BY from_id ORDER BY timestamp DESC LIMIT 1

- Marcus Recck

---

@BumbleB2na 你的意思是 LIMIT 1 吗？ - Li0liQ

现在你将得到所有消息中的最后一条消息，你需要一个where条件。 - jcho360

@BumbleB2na：其实这也是SQL，只不过它是MySQL SQL，而不是SQL Server SQL（又称Transact-SQL）。 :) - Andriy M

不需要使用“limit 1”，因为“group by from_id”会将输出限制为每个from_id一行。 - GarethD

它将会给你相同的结果，只不过获得的结果将按时间戳排序。 - Abhishek Goel