MySQL按用户分组对话并展示最后一条消息的实现问题

我来帮你搞定这个SQL问题！你现在的需求是列出utenti表的所有用户，同时给和id_utente=1有过消息往来的用户展示双方的最后一条消息，还要排除和用户1无关的消息，之前的SQL要么返回全部消息要么出现用户重复，核心问题应该是没精准筛选出每个对话的最新记录，我给你两种靠谱的实现方式：

窗口函数ROW_NUMBER()可以很方便地给每个对话的消息按时间排序，直接取最新的那一条：

WITH last_messages AS (
    SELECT
        -- 统一识别对话的另一方用户ID：如果是发给1的，取发件人；如果是1发的，取收件人
        CASE 
            WHEN msg_to = 1 THEN msg_from
            WHEN msg_from = 1 THEN msg_to
        END AS partner_id,
        msg_text,
        msg_date,
        -- 按对话分组，消息时间倒序排，给每条消息标序号，最新的消息序号为1
        ROW_NUMBER() OVER (
            PARTITION BY 
                CASE 
                    WHEN msg_to = 1 THEN msg_from
                    WHEN msg_from = 1 THEN msg_to
                END
            ORDER BY msg_date DESC
        ) AS rn
    FROM messaggi
    -- 只保留和用户1相关的消息
    WHERE msg_to = 1 OR msg_from = 1
)
SELECT
    u.id_utente,
    u.nome, -- 这里替换成你用户表实际的名称字段，比如username
    lm.msg_text AS last_msg_content,
    lm.msg_date AS last_msg_time
FROM utenti u
-- 左连接保证所有用户都能被列出，只取每个对话的最新消息（rn=1）
LEFT JOIN last_messages lm ON u.id_utente = lm.partner_id AND lm.rn = 1
ORDER BY 
    -- 把有消息的用户排在前面，方便查看
    CASE WHEN lm.msg_date IS NOT NULL THEN 0 ELSE 1 END,
    u.id_utente;

代码说明 #

1. CTE last_messages：先筛选出所有和用户1相关的消息，然后给每个对话的消息按时间倒序标序号，最新的消息序号是1
2. 主查询：左连接用户表和处理后的消息表，这样所有用户都会被列出，没有消息往来的用户，消息相关字段会显示NULL
3. 排序：让有消息的用户优先显示，浏览起来更直观

如果你的数据库不支持窗口函数，可以用子查询找每个对话的最新消息时间，再匹配对应的消息内容：

SELECT
    u.id_utente,
    u.nome, -- 替换成你实际的用户名称字段
    m.msg_text AS last_msg_content,
    m.msg_date AS last_msg_time
FROM utenti u
LEFT JOIN (
    SELECT
        CASE 
            WHEN msg_to = 1 THEN msg_from
            WHEN msg_from = 1 THEN msg_to
        END AS partner_id,
        msg_text,
        msg_date
    FROM messaggi m1
    WHERE (msg_to=1 OR msg_from=1)
    -- 子查询找到当前对话的最新消息时间
    AND msg_date = (
        SELECT MAX(msg_date)
        FROM messaggi m2
        WHERE 
            (m2.msg_to = 1 AND m2.msg_from = m1.msg_from)
            OR (m2.msg_from = 1 AND m2.msg_to = m1.msg_to)
    )
) m ON u.id_utente = m.partner_id
ORDER BY 
    CASE WHEN m.msg_date IS NOT NULL THEN 0 ELSE 1 END,
    u.id_utente;

注意事项 #

• 记得把u.nome替换成你utenti表中实际的用户名称字段（比如username、full_name等）
• 如果存在同一时间发送多条消息的情况，方案二会返回所有同时间的最新消息，方案一则只会取其中一条，你可以根据需求调整（比如添加DISTINCT或者修改排序条件）

内容的提问来源于stack exchange，提问作者Mr.Web