MYSQL：查詢聊天消息的最後轉換並按最新消息排序

我在對每個使用者的最後一個 convo 消息進行查詢排序時遇到問題。這是我在 2 張桌子上的目前記錄：

我想達到以下結果：

我目前的查詢是：

SELECT `messages`.*, `users`.`username`, `u2`.`username` as `to_username`,
CASE WHEN to_user_id = 1
   THEN `users`.`username`
   ELSE u2.username
END as participant
FROM `messages` 
LEFT JOIN `users` ON `messages`.`from_user_id`=`users`.`id` 
LEFT JOIN `users` as `u2` ON `messages`.`to_user_id`=`u2`.`id` 
INNER JOIN (SELECT max(id) as lastmsgId FROM messages where to_user_id = 1 or from_user_id =1 GROUP BY to_user_id) m2 ON `messages`.`id`=`m2`.`lastmsgId` 
GROUP BY participant  
ORDER BY `messages`.`created_at` DESC

我的查詢結果是：

第 4 條和第 5 條消息沒有正確排序我希望顯示第 4 條消息而不是第 5 條消息。它顯示較舊的消息而不是最新消息。我找不到關於如何顯示正確排序的解決方案。

非常感謝任何幫助和建議。

假設沒有自我消息：

SELECT t1.*
FROM messages t1
JOIN ( SELECT LEAST(from_user_id, to_user_id) user1,
             GREATEST(from_user_id, to_user_id) user2,
             MAX(created_at) created_at 
      FROM messages t2
      GROUP BY user1, user2 ) t3  ON t1.from_user_id IN (t3.user1, t3.user2)
                                 AND tm1.to_user_id IN (t3.user1, t3.user2)
                                 AND t1.created_at = t3.created_at ;

如果需要，加入users表。

如果id (1) < id (2)=>created_at (1) < created_at (2)那麼您可以將所有出現的 替換created_at為id。

不確定 mysql 中的性能，但是：我假設 id 可以用作 order by 以及它的身份，但是：

抱歉，沒有格式化，在手機上。

按最少（from_user_id，to_user_id），最大（from_user_id，to_user_id）從消息組中選擇最大值（id）

在此基礎上，您可以根據 id 加入以獲取其餘列

引用自：https://dba.stackexchange.com/questions/290346