mysql-查询具有特定条件的消息的线程列表 [英] mysql - query for thread list of messages with specific conditions

问题描述

我有三个表：用户，请求和消息。我想获得类似android / ios消息应用程序启动时显示的线程列表的信息。

I have three tables: user, request and message. I would like to get something like the thread list that android/ios message app shows when launched.

用户

id  username
1   a
2   b
3   c

请求

id
1
2
3

消息

Id  request_id     sender_id   receiver_id message timestamp
1   1       1       2       asd 2013-06-10 06:45
2   1       1       3       sdf 2013-06-10 06:42
3   2       1       1       dfg 2013-06-10 06:41
4   2       1       2       fgh 2013-06-11 06:40
5   2       1       2       ghj 2013-06-12 07:45
6   2       2       1       jkl 2013-06-10 06:45
7   3       3       1       zxc 2013-06-10 03:45
8   3       3       1       xcv 2013-06-10 05:45

我想得到的是这样的：

线程

request_id  sender_name receiver_name   last_message    last_timestamp
1       a       b       asd     2013-06-10 06:45
1       a       c       sdf     2013-06-10 06:42
2       a       b       ghj     2013-06-12 07:45
2       a       a       dfg     2013-06-10 06:41
3       c       a       xcv     2013-06-10 05:45

在这里，每行和last_message的request_id + sender_id + receive_id是唯一的，last_timestamp显示了消息表中的最新条目。顺序将为last_timestamp降序。如何获得此表？

Here, request_id + sender_id + receiver_id is unique for each row and last_message, last_timestamp shows the latest entry from the message table. The order will be last_timestamp descending. How do I get this table?

推荐答案

您要逐组最大值，可以通过加入 Messages 表到一个子查询，该子查询标识每个组的标识（最大）时间戳：

You want the groupwise maximum, which can be found by joining the Messages table to a subquery that identifies the identifying (maximal) timestamp for each group:

SELECT   Message.request_id,
         Sender.username   AS sender_name,
         Receiver.username AS receiver_name,
         Message.message   AS last_message,
         Message.timestamp AS last_timestamp
FROM     Message NATURAL JOIN (
           SELECT   request_id,
                    sender_id,
                    receiver_id,
                    MAX(timestamp) timestamp
           FROM     Message
           GROUP BY request_id, sender_id, receiver_id
         ) t
    JOIN User Sender   ON   Sender.id = Message.sender_id
    JOIN User Receiver ON Receiver.id = Message.receiver_id
ORDER BY Message.request_id, last_timestamp DESC

在 sqlfiddle 。

请注意，由于我在上面的评论：

您说 顺序将是 last_timestamp 降序，但在给定的示例中，消息'ghj'（已发送在所有其他消息之后两天）不仅出现在所有记录的中间，而且出现在所有具有相同 request_id 的消息的中间。请说明结果集所需的排序顺序？

You say that "the order will be last_timestamp descending", but in the given example the message 'ghj' (sent two days after all the other messages) appears not only in the middle of all the records but furthermore in the middle of all those with the same request_id too. Please clarify the desired sort order of the resultset?

这篇关于mysql-查询具有特定条件的消息的线程列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！