获取用户1各对话最后一条消息的SQL查询需求

嘿，这个需求很常见，我来给你两种靠谱的解决方案，适配不同的SQL环境！首先我先假设你的messages表包含这些核心字段：sender_id（发送者ID）、receiver_id（接收者ID）、content（消息内容）、created_at（消息发送时间，用来判断消息顺序）。如果你的表字段名不一样，对应替换就行。

方法一：用窗口函数（推荐，清晰易维护） #

窗口函数是现代SQL的标配，逻辑清晰，扩展性强：

WITH ranked_messages AS (
    SELECT
        content,
        -- 给对话分组做个标识，方便后续查看和调试
        CASE
            WHEN sender_id = 1 THEN receiver_id
            ELSE sender_id
        END AS conversation_partner,
        ROW_NUMBER() OVER (
            PARTITION BY 
                -- 关键：把双向对话归为同一组，比如1<->2的对话不管谁发都算一组
                LEAST(sender_id, receiver_id),
                GREATEST(sender_id, receiver_id)
            ORDER BY created_at DESC
        ) AS rn
    FROM messages
    -- 只筛选用户1参与的对话
    WHERE sender_id = 1 OR receiver_id = 1
)
SELECT content
FROM ranked_messages
WHERE rn = 1;

逻辑解释： #

• LEAST(sender_id, receiver_id)和GREATEST(sender_id, receiver_id)会把(1,2)和(2,1)统一成同一个分组键，确保用户1和对方的双向对话被归为一组。
• ROW_NUMBER()会给每个分组内的消息按发送时间倒序排名，排名为1的就是该组的最后一条消息。
• 最后筛选出排名为1的记录，就是你要的两组对话的最后一条消息啦。

方法二：关联子查询（兼容老版本SQL） #

如果你的数据库不支持CTE或者窗口函数（比如一些老版本的MySQL），可以用关联子查询的方式：

SELECT m.content
FROM messages m
WHERE (m.sender_id = 1 OR m.receiver_id = 1)
AND m.created_at = (
    SELECT MAX(created_at)
    FROM messages
    WHERE 
        -- 匹配同一组对话的双向情况
        (sender_id = m.sender_id AND receiver_id = m.receiver_id)
        OR (sender_id = m.receiver_id AND receiver_id = m.sender_id)
);

逻辑解释： #

• 外层查询先筛选出用户1参与的所有消息。
• 子查询针对每条消息，找出对应的同一对话组里的最新发送时间，外层消息的时间和这个最大时间匹配上的，就是该组的最后一条消息。

补充说明： #

如果你的表用自增的message_id来判断消息顺序（ID越大越晚），可以把created_at替换成message_id，用MAX(message_id)来查询，效果是一样的。

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