查询返回了额外的行数。

Question

我有一个简单聊天的表格：

messages table structure
+----+---------+-----------+---------+------+------+
| id | to_user | from_user | message | read | sent |
+----+---------+-----------+---------+------+------+

我需要获取每个会话的列表，格式如下。

Username ----  Message ---- Date

我将使用以下查询来完成它：

SELECT *
FROM `messages`
WHERE `sent`
IN (
   SELECT MAX( `sent` )
   FROM `messages`
   WHERE `from_user` = '1' --id of user who is requesting the list
   OR `to_user` = '1'  --id of user who is requesting the list
   GROUP BY `to_user` , `from_user`
   )
LIMIT 0 , 30

这几乎可以正常工作，但我的问题是它并没有返回我该对话的最后一条消息，而是每个用户的最后一条消息，所以假设用户1和2正在交谈，我得到了以下列表：

+----+---------+-----------+-----------------------+------+---------------------+
| id | to_user | from_user | message               | read | sent                |
+----+---------+-----------+-----------------------+------+---------------------+
|  3 |       2 |         1 | Message 1 from user 1 |    0 | 2012-01-11 13:20:54 |
|  4 |       1 |         2 | Message 1 from user 2 |    0 | 2012-01-11 13:24:59 |
+----+---------+-----------+-----------------------+------+---------------------+

我希望只获取最后一条消息，即4，因为第四条记录中sent字段最高，那么我该如何解决？

编辑 删除group by后，即使用户与多个用户交谈，我也只会收到一条消息。

- Linas

---

请查看我的答案，那才是正确的答案（而不是你接受的那个错误的答案）。 - Bohemian

Answer 1

你正在获取每个用户的最后一条消息，因为你已经对 to_user 和 from_user 进行了 GROUP BY。

在你的查询中没有必要使用 GROUP BY 子句。

- Shraddha

Answer 2

SELECT *
FROM `messages`
WHERE `sent`
IN (
   SELECT MAX( `sent` )
   FROM `messages`
   WHERE (`from_user` = '1' OR `to_user` = '1')
   )
LIMIT 0 , 30

我相信groupby将把它们合并。

- Silvertiger

Answer 3

以下是如何操作的步骤：

SELECT *
FROM (SELECT * 
  FROM messages
  WHERE from_user = ?
  OR to_user = ?
  ORDER by from_user, to_user, sent DESC
) x
GROUP BY from_user, to_user
ORDER BY sent DESC
LIMIT 1;

在MySQL中，如果使用group by对其他列不进行聚合，则会返回每个组的第一行。通过从有序行集（内部查询）中选择，我们可以获得每个对话的最新行。

- Bohemian

---

实际上这使情况变得更糟，我不仅需要最后一条消息...我需要获取每个对话的最后一条消息。 - Linas

这段代码与我的当前查询执行相同的操作，它获取每个对话中的最后两条消息，我需要找到一种方法只获取最后一条消息。当我思考时，实际上是获取了每个用户的最后一条消息，所以我需要找到一种方法只获取一个用户的最后一条消息，这有点难以解释... - Linas

@Linas 我现在明白了！一次对话是由A和B之间进行的。您想要他们之间的最后一条消息-无论是从A到B还是从B到A-方向并不重要，只有A和B的组合。这是一个非常有趣的问题，但幸运的是我有解决方案-请参见编辑后的答案。只需要在查询末尾进行一些微调即可。 - Bohemian

是的，这是正确的，但现在它给我带来了另一个问题，您知道我需要创建用户进行过的所有对话的历史记录，所以现在这只能获取最后一条消息，但我需要为用户进行的每个对话执行此操作。 - Linas

你的问题在于同一行将适用于“from”和“to”用户 - 这是一个棘手的问题。我认为我已经给了你足够的帮助。祝你好运。 - Bohemian

Answer 4

在你的in语句中删除group by子句--在这种情况下它是无用的。它为每个不同的to_user和from_user配对返回一个sent时间戳。你真正想要的只是to_user或from_user等于某个值时的最大sent。去掉group by，你将返回仅有一条记录，显示用户发送或接收的最新消息。

看起来像这样：

SELECT *
FROM `messages`
WHERE `sent`
IN (
   SELECT MAX( `sent` )
   FROM `messages`
   WHERE `from_user` = '1' --id of user who is requesting the list
   OR `to_user` = '1'  --id of user who is requesting the list
   )
LIMIT 0 , 30

- Eric

---

-1 这实际上并不起作用 - 它将返回在那个时间发送的所有消息，无论是谁发送/接收的。 - Bohemian