mysql 线程列表,mysql-查询具有特定条件的消息的线程列表

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.

User

id username

1 a

2 b

3 c

Request

id

1

2

3

Message

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

What I would like to get is something like this:

Thread

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

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?

解决方案

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

See it on sqlfiddle.

Note that the order of my resultset differs from that expected in your question for the reasons highlighted in my comment above:

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?