如何用SQL查询聊天系统中用户的最后一条消息

嘿，我明白你现在的需求——要实现类似Facebook消息界面里的「最新对话消息」展示对吧？针对你现有的message表（字段id,userTo,userFrom,message,date），我给你两种实用的SQL方案，适配不同的数据库场景：

方法一：子查询+关联（兼容大部分数据库） #

这种方法适合没有窗口函数的老版本数据库（比如MySQL 5.x）。核心思路是先找出每一组对话的最新消息时间，再关联回原表拿到对应的消息内容。

因为聊天对话是双向的（比如用户A发给B，和B发给A属于同一个对话），我们用LEAST()和GREATEST()函数把两个用户ID固定成统一的顺序，确保同一场对话被归为一组：

SELECT m.*
FROM message m
INNER JOIN (
    SELECT
        LEAST(userTo, userFrom) AS user1,
        GREATEST(userTo, userFrom) AS user2,
        MAX(date) AS latest_date
    FROM message
    -- 如果要查询某个特定用户的对话，比如用户ID为100，添加下面的条件
    -- WHERE userTo = 100 OR userFrom = 100
    GROUP BY user1, user2
) latest ON (
    (m.userTo = latest.user1 AND m.userFrom = latest.user2) 
    OR (m.userTo = latest.user2 AND m.userFrom = latest.user1)
) AND m.date = latest.latest_date;

方法二：窗口函数（简洁高效，适用于现代数据库） #

如果你的数据库支持窗口函数（比如MySQL 8+、PostgreSQL、SQL Server等），用ROW_NUMBER()会更简洁直观：

SELECT id, userTo, userFrom, message, date
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY LEAST(userTo, userFrom), GREATEST(userTo, userFrom)
            ORDER BY date DESC
        ) AS rn
    FROM message
    -- 同样，筛选特定用户的话添加：WHERE userTo = 100 OR userFrom = 100
) ranked
WHERE rn = 1;

• 这里PARTITION BY把同一场对话的消息分成一组，ORDER BY date DESC让最新的消息排在最前面
• ROW_NUMBER()给每组的消息编号，最新的消息编号为1，所以我们只取rn=1的记录

注意事项 #

• 如果同一场对话在同一时间有多条消息（date完全相同），方法一会返回所有这些消息，方法二则只会返回其中一条（如果想保留所有同时间的最新消息，可以把ROW_NUMBER()换成RANK()）
• 记得给date字段加索引，这样查询会更高效，尤其是数据量大的时候

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