查询：如何获取每个用户对话的最新消息？-Java 学习之路

我有一个非常简单但很重要的会话表（created_by是发送消息的用户的代码） . 然后，我有一个conversations_messages表（sent_by是发送消息的用户的代码，消息是我的消息文本，created_at是时间戳） . 最后，我有一个conversations_users表，其中包含user_id（收到消息的用户的代码）和created_by（发送消息的用户的代码） .

我正在尝试实现收件箱的功能，我想显示每个对话的最后一条消息 . conversations_users中的user_id是收到邮件的用户和正在查询其收件箱的用户 .

我尝试了这个查询，但我不知道如何处理created_at时间戳（在表conversations_messages中），以便获得每个对话的最新消息 .

select CM.id, CM.message, CM.created_at, C.id as 'conversation_id' from conversations_users CU 
    join conversations C on CU.conversation_id = C.id join conversations_messages CM on C.id = CM.conversation_id where CU.user_id = 1 and CM.created_at = '2015-03-10 14:18:02'

我必须替换'2015-03-10 14:18:02'以获取最新的created_at时间戳，这是我的问题所在 .

我的表是：

conversations_users：id，conversation_id，user_id，created_by
个对话：id，created_by
conversations_messages：id，conversation_id，sent_by，message，created_at

谢谢！

2 回答

SELECT cm.*
FROM conversations_messages cm
JOIN conversations c ON cm.conversation_id = c.id
JOIN conversations_users cu ON cu.conversation_id = c.id
WHERE cu.user_id = 1
AND NOT EXISTS (    SELECT 'a'
                    FROM conversations_messages cm2
                    WHERE cm2.conversation_id = cm.conversation_id
                    AND cm2.created_at > cm.created_at
                    )
GROUP BY c.id

回复于 2024-05-05T12:17:10+08:00

这是使用 max 聚合将表连接回自身的一种方法，按每个对话分组：

select CM.id, CM.message, CM.created_at, C.id as 'conversation_id' 
from conversations_users CU 
    join conversations C on CU.conversation_id = C.id 
    join conversations_messages CM on C.id = CM.conversation_id 
    join (
        select max(created_at) maxcreated_at, conversation_id 
        from conversations
        group by conversation_id 
    ) T on T.maxcreated_at = CM.created_at 
           and T.conversation_id = CM.conversation_id 
where CU.user_id = 1

回复于 2024-05-05T12:17:10+08:00

查询：如何获取每个用户对话的最新消息？

2 回答

相关问题