如何通过GROUP BY与Max ID查询实现多用户类型聊天机器人的外部用户最新会话汇总

你的原SQL只筛选了外部用户发送给ID=0的消息，完全没考虑内部用户回复给他们的记录——这就是结果不符合预期的核心原因。我们需要先锁定每个外部用户的所有相关聊天记录（自己发的消息+别人回复他们的消息），再从中提取最新的（chatId最大的）那一条。

修改后的SQL语句 #

SELECT 
    u.name,
    c.chatFrom,
    c.chatTo,
    c.chatId,
    c.chatValue
FROM TBL_CHAT c
INNER JOIN TBL_USER u ON c.chatFrom = u.userId
INNER JOIN (
    -- 子查询：找出每个外部用户的最新聊天记录ID
    SELECT 
        ext_user.userId AS external_user_id,
        MAX(c_all.chatId) AS latest_chat_id
    FROM TBL_USER ext_user
    -- 仅针对外部用户
    WHERE ext_user.userType = 'external'
    -- 关联该外部用户的所有相关聊天记录：自己发的 OR 别人回复的
    LEFT JOIN TBL_CHAT c_all 
        ON c_all.chatFrom = ext_user.userId 
        OR c_all.chatTo = ext_user.userId
    GROUP BY ext_user.userId
) latest_chats ON c.chatId = latest_chats.latest_chat_id
ORDER BY c.chatId DESC;

逻辑拆解 #

1. 子查询核心作用：

   • 先从用户表中筛选出所有外部用户
   • 关联聊天表，把每个外部用户的两类聊天记录（自己发送的、收到的回复）全部纳入范围
   • 按外部用户ID分组，取每组最大的chatId（也就是该用户的最新聊天记录标识）

2. 主查询关联获取详情：

   • 用子查询得到的最新chatId，关联回聊天表拿到完整的聊天内容
   • 再关联用户表，获取消息发送者的名称

执行这个语句后，就能得到你期望的结果：每个外部用户的最后一条聊天记录，无论这条记录是用户自己发送的，还是内部用户回复他们的。

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