mysql - SQL:选择对话 GROUP BY 与另一个表中的最后一条记录

Question

我有以下问题。我有两张 table :

baz_conversations

con_id, con_users, con_date

baz_con_messages

message_id, message_con_id, message_text, message_author

我想显示我的所有对话 - 表 baz_conversations 中的 con_users 必须包含 myID 以及该对话的另一个表中的最后一条消息。我花了太多时间来编写这个简单的 sql select，但它从表 baz_con_messages 中选择第一条消息而不是最后一条消息。谢谢您的回答。这是我的代码:

SELECT m.*, c.* FROM baz_conversations AS c RIGHT JOIN 
                     baz_con_messages AS m ON c.con_id = m.message_con_id
WHERE c.con_users LIKE '%1,%' 
GROUP BY c.con_id 
ORDER BY m.message_id DESC 
LIMIT 1

Answer 1

我想你想要这样的东西:

SELECT m.*, c.*
FROM baz_conversations c JOIN 
     baz_con_messages m
     ON c.con_id = m.message_con_id
WHERE c.con_users LIKE '%1,%' AND
      not exists (select 1
                  from baz_con_messages m2
                  where m2.message_con_id = m.message_con_id and
                        m2.message_id > m.message_id
                 );