查询返回其他行 [英] Query returns additional rows

问题描述

我有这种用于简单聊天的表格:

I have this kind of table for simple chat:

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

我需要获取每个看起来像这样的对话的列表

And i need to get list of each conversation which looks like that

Username ----  Message ---- Date

我正在使用此查询来做到这一点:

I am using this query to do it:

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

这几乎可以正常工作，我的问题是它返回的不是我的对话的 last 消息，而是每个用户的最后一条消息，因此，假设用户1和2正在通话，我正在获取此列表，这就是我得到的:

And this works almost fine, my problem is that it returns me not the last message of that conversation but last message from each user so let's say user 1 and 2 is talking and i'm getting this list, this is what i get:

+----+---------+-----------+-----------------------+------+---------------------+
| 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字段是第4条记录中的最高记录，所以我该如何解决呢?

And i would like to get only last message which is 4, cause sent field is the highest in 4th record so how can i solve it?

编辑:删除group by后，即使用户与多个用户通话，我也只收到一条消息

EDIT After deleting group by i'm getting only one message even if user was talking with more than one user

推荐答案

操作方法如下:

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中，按不包含的组将其他列进行汇总，则返回每个组的 first 行.通过选择一个有序行集(内部查询)，我们为每个会话获取了最近行.

In mysql, a group by without aggregating the other columns returns the first row for each group. By selecting form an ordered row set (the inner query) we get the most recent row for each conversation.

这篇关于查询返回其他行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！