MySQL获取最新的对话消息

小编典典

MySQL获取最新的对话消息

sql

我正在开发一个应用程序，用户可以在其中与其他用户聊天（1对1，而不是群聊）。我有一个MySQL表，用于存储来自每个用户的所有消息，例如：

from_id   to_id          message             time
abc123    def456         Hello               789
def456    abc123         What's up?          1234`
def456    abc123         How was last night? 2345
abc123    p0tat0         I missed the bus    3456
def456    p0tat0         I hate you :(       4567`
def456    another_user   I hate Potato!      5678`

如何从AND获取最新消息，abc123从最新到最旧排序，例如：

from_id to_id message time

abc123 p0tat0 I missed the bus 3456

def456 abc123 How was last night? 2345

time 如果重要的话，在消息表中将始终以升序排列。

任何帮助将不胜感激。谢谢^。^

SELECT *, 'from' as directionFROM messages WHERE from_username='admin' AND 'time' = ( SELECT MAX('time') FROM messages WHERE from_username='admin' OR to_username='admin')

UNION ALL

SELECT *, 'to' as directionFROM messages WHERE to_username='admin' AND 'time' = ( SELECT MAX('time') FROM messages WHERE to_username='admin' ORto_username='admin' )

阅读 144

2021-03-17

共1个答案

小编典典

试试这个

SELECT * FROM
messageTable
WHERE from_id='abc123'
AND `time` = ( SELECT MAX(`time`) FROM messageTable WHERE from_id='abc123' )

UNION ALL

SELECT * FROM
messageTable
WHERE to_id='abc123'
AND `time` = ( SELECT MAX(`time`) FROM messageTable WHERE to_id='abc123' )

2021-03-17