MySQL查询问题：获取指定用户收发的最新消息及关联用户详情

我来帮你搞定这个问题，先说说你原来的SQL哪里出了问题，再给你正确的解决方案。

原SQL的问题分析 #

1. 字段名不匹配：你的表结构里message_log的发件人字段是msg_from_user，但原SQL里用了msg_from_user_id，这会直接导致字段不存在的错误，肯定跑不通。
2. 逻辑覆盖不全：原SQL只处理了指定用户作为收件人时，发件人的最新消息，但完全没考虑指定用户作为发件人时的收件人消息场景，不符合你“收发最新消息”的需求。
3. 子查询逻辑偏差：原SQL的子查询是获取每个发件人自己的最新消息，而不是和指定用户对话的最新消息，这和你要的结果完全不对路。

正确的解决方案 #

我们的核心需求是：获取指定用户（比如示例里的234）与每个对话对象之间的最新一条消息，同时附带对方的用户详情。下面是两种可行的写法：

方法一：使用CTE（适用于MySQL 8.0+、PostgreSQL等支持CTE的数据库） #

-- 替换这里的234为你要查询的指定user_id
SET @target_user_id = 234;

WITH user_messages AS (
    SELECT
        msg_id,
        msg_from_user,
        msg_to_user,
        msg_message,
        msg_datetime,
        -- 确定每个消息对应的对话伙伴ID
        CASE 
            WHEN msg_from_user = @target_user_id THEN msg_to_user
            ELSE msg_from_user
        END AS partner_id
    FROM message_log
    -- 筛选指定用户参与的所有消息（发件或收件）
    WHERE msg_from_user = @target_user_id OR msg_to_user = @target_user_id
),
latest_messages AS (
    SELECT
        partner_id,
        MAX(msg_id) AS latest_msg_id -- 用msg_id判断最新更可靠（自增ID）
    FROM user_messages
    GROUP BY partner_id -- 按对话伙伴分组，取每组最新消息ID
)
-- 关联用户表获取伙伴详情，拼接最终结果
SELECT
    u.user_id,
    u.user_name,
    u.user_profile_pic,
    m.msg_id,
    m.msg_from_user AS msg_from,
    m.msg_to_user AS msg_to,
    m.msg_message,
    m.msg_datetime
FROM latest_messages lm
JOIN user_messages m ON lm.latest_msg_id = m.msg_id
JOIN user_table u ON u.user_id = lm.partner_id
ORDER BY m.msg_datetime DESC; -- 按消息时间倒序，最新的在前

方法二：兼容旧版本数据库（不使用CTE） #

-- 替换这里的234为你要查询的指定user_id
SELECT
    u.user_id,
    u.user_name,
    u.user_profile_pic,
    m.msg_id,
    m.msg_from_user AS msg_from,
    m.msg_to_user AS msg_to,
    m.msg_message,
    m.msg_datetime
FROM message_log m
JOIN user_table u ON 
    -- 关联对话伙伴的用户信息
    (m.msg_from_user = 234 AND u.user_id = m.msg_to_user) 
    OR (m.msg_to_user = 234 AND u.user_id = m.msg_from_user)
WHERE m.msg_id IN (
    -- 子查询获取每个对话伙伴的最新消息ID
    SELECT MAX(msg_id)
    FROM message_log
    WHERE msg_from_user = 234 OR msg_to_user = 234
    GROUP BY 
        CASE 
            WHEN msg_from_user = 234 THEN msg_to_user
            ELSE msg_from_user
        END
)
ORDER BY m.msg_datetime DESC;

逻辑说明 #

1. 首先筛选出指定用户参与的所有消息，不管是发还是收。
2. 对每个对话伙伴（和指定用户聊天的另一方），找出双方之间的最新消息（用自增的msg_id判断比msg_datetime更准确，避免时间重复的情况）。
3. 最后把最新消息和用户表关联，获取对话伙伴的详情，得到你期望的结果格式。

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