mysql - 最简单的私信系统【单表】

Question

我想创建类似 tinder/whatsapp 的消息。

我有一个名为“消息”的表，我想显示用户之间的所有对话(最后一条消息)

表结构:

message_id | user_id | recipient_id | message | status | date

示例行:

1 | 1 | 2 | Hello | 0 | 2016-03-26 12:00:00
2 | 2 | 1 | Hi | 0 | 2016-03-26 12:05:00
3 | 1 | 3 | Are you there? I want meet you! :P | 0 | 2016-03-26 12:20:00
4 | 1 | 2 | How are you? | 0 | 2016-03-26 12:10:00
5 | 1 | 2 | Hello?? | 0 | 2016-03-26 12:15:00
6 | 5 | 1 | Hi :D | 0 | 2016-03-26 15:00:00

因此，结果应该是(对于 user_id == 1):

3 | 1 | 3 | Are you there? I want meet you! :P | 0 | 2016-03-26 12:20:00
5 | 1 | 2 | Hello?? | 0 | 2016-03-26 12:15:00 ==> **This should show the last message between this 2 person**
6 | 5 | 1 | Hi :D | 0 | 2016-03-26 15:00:00

我已经尝试过这个:

SELECT *
FROM
(
SELECT * from messages
where user_id=1 or recipient_id=1
ORDER BY date DESC
) m
GROUP BY user_id

但它只显示来自 user_id 的最后一条消息，而不是来自两者(user_id &&recipient_id)的最后一条消息

Answer 1

mysql 5.7 或 8 哪个版本？

select 
    m.* 
from 
    messages m 
join 
(
SELECT 
    max(date) as udate
    ,user_id as id
from 
    messages
where 
    user_id=1

UNION ALL

SELECT 
    max(date) as udate
    ,recipient_id as id
from 
    messages
where 
    recipient_id=1
) t
on m.date = t.udate
and m.user_id = 1 and m.recipient_id = 1