如何关联users、chat表查询每个会话的最新消息，实现类似WhatsApp的最近消息列表

你原有SQL存在两处问题：

1. 语法错误：WHERE条件中user_id ='1 OR friend_id =1的引号位置错误，会导致条件判断失效
2. 缺少和users表的关联逻辑，也没有正确提取好友ID映射到用户表

调整后的可直接运行SQL #

SELECT 
    -- 提取好友ID：当前用户为1，聊天记录中非1的一侧就是好友ID
    CASE WHEN c.user_id = 1 THEN c.friend_id ELSE c.user_id END AS friend_id,
    u.username,
    c.message,
    u.photo,
    u.status
FROM chat c
-- 关联用户表拉取好友的账号信息
INNER JOIN users u 
    ON u.user_id = CASE WHEN c.user_id = 1 THEN c.friend_id ELSE c.user_id END
WHERE c.id IN (
    SELECT max(id) 
    FROM chat 
    WHERE user_id = 1 OR friend_id = 1
    GROUP BY message_token
)
-- 按消息时间倒序，保证最新会话排在最前
ORDER BY c.msg_time DESC;

逻辑说明 #

• 内层子查询保留了你原有按message_token分组取单会话最新消息的逻辑，仅修正了引号错误
• 用CASE WHEN判断当前聊天记录的收发双方，准确提取对应好友的ID
• 用好友ID关联users表，即可拿到对应用户的用户名、头像、在线状态信息

如果你的数据库支持窗口函数，还可以用下面性能更优的写法，避免IN查询在数据量大时的效率问题：

WITH ranked_chat AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY message_token ORDER BY id DESC) AS rn
    FROM chat
    WHERE user_id =1 OR friend_id =1
)
SELECT
    CASE WHEN rc.user_id =1 THEN rc.friend_id ELSE rc.user_id END AS friend_id,
    u.username,
    rc.message,
    u.photo,
    u.status
FROM ranked_chat rc
INNER JOIN users u ON u.user_id = CASE WHEN rc.user_id =1 THEN rc.friend_id ELSE rc.user_id END
WHERE rc.rn =1
ORDER BY rc.msg_time DESC;

两种写法基于你提供的示例数据运行，都可以得到你需要的输出结果。

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