mysql - 未回复的消息 - MYSQL

Question

我有一个名为messages的表，用于存储用户消息，表结构如下所示:

消息:

id   from_id    to_id  content        read_at
1    1          2       ABC           2014-02-28 16:36:39
2    2          1       BCC           2014-02-29 16:36:39
3    1          2       EFG           2014-02-29 18:50:39
4    4          2       GHJ           2014-02-29 16:36:39
5    2          4       MNX           
6    15         10      LKH           2014-02-30 16:36:39
7    20         6       LKH           
8    21         11      LKH           2014-03-03 17:46:40

如何从上表中获取未回复的消息？

预期输出:

   id      from_id  to_id  read_at
   6       15       10     2014-02-30 16:36:39
   8       21       11     2014-03-03 17:46:40

新消息:

用户收到消息但尚未阅读。

示例:

 id   from_id    to_id  content        read_at
  5    2        4       MNX           
  7    20       6       LKH           

未回复的消息:

用户收到一条消息并已阅读，但尚未回复。但是，只有对话的第一条消息才会被视为未回复消息。

示例:

id   from_id    to_id  content        read_at
8    21         11      LKH           2014-03-03 17:46:40

编辑:我尝试过的查询:

SELECT from_id, to_id, count(id) as count
  FROM messages
  where read_at is not null
 GROUP BY LEAST(from_id, to_id), 
          GREATEST(from_id, to_id)
 having count < 2;

Answer 1

以下返回预期结果。我还测试了向同一用户以及从不同用户向同一用户等发送的多条未回复消息，以确认 GROUP BY 和 MIN(date_read)仅返回未回复消息的最早行，但仍显示每个发件人/发件人用户组合。

http://sqlfiddle.com/#!2/c8a80/1

SELECT outgoing.*, MIN(outgoing.date_read) 
FROM   messages AS outgoing 
       LEFT JOIN messages AS incoming 
              ON outgoing.from_id = incoming.to_id 
                 AND outgoing.to_id = incoming.from_id 
WHERE  incoming.id IS NULL 
       AND outgoing.date_read IS NOT NULL 
GROUP  BY to_id, from_id; 

注意，通过将表左连接到自身，其中 to_id 是 from_id，反之亦然，我们知道只有连接表返回 的连接行>NULL 表示未读或未回复。

然后，首先使用聚合 MIN(outgoing.date_read) 列以及 to_id 上的 GROUP BY，然后使用 from_id，我们知道我们只会获得每个分组的最早日期，并且它将显示每个 to_id 的所有 from_id 而不会重复。