MySQL如何按聊天表最新记录排序查询对应用户数据？

实现逻辑 #

• 先从Chat表筛选出和用户6相关的聊天记录，按交互对象分组，提取每组的最大聊天ID作为排序依据
• 关联User表匹配交互对象的完整用户信息
• 过滤掉用户6自身，按最大聊天ID降序排序得到结果

适配需求的查询语句 #

你给出的场景中所有消息的发送方都是ID=6的用户，直接使用以下语句即可：

SELECT u.*
FROM User u
INNER JOIN (
    SELECT receiver, MAX(id) AS max_chat_id
    FROM Chat
    WHERE sender = 6
    GROUP BY receiver
) chat_sort ON u.id = chat_sort.receiver
WHERE u.id != 6
ORDER BY chat_sort.max_chat_id DESC;

拓展：兼容双向聊天场景 #

如果需要统计和用户6有双向收发消息的所有用户，按最近一次交互时间排序，可使用以下语句：

SELECT u.*
FROM User u
INNER JOIN (
    SELECT
        -- 提取和用户6聊天的对方ID
        CASE WHEN sender = 6 THEN receiver ELSE sender END AS contact_id,
        MAX(id) AS max_chat_id
    FROM Chat
    WHERE sender = 6 OR receiver = 6
    GROUP BY contact_id
) chat_sort ON u.id = chat_sort.contact_id
WHERE u.id != 6
ORDER BY chat_sort.max_chat_id DESC;

内容的提问来源于stack exchange，提问作者Aarav