如何为每个chat_id获取最新的message记录？

你的原查询仅对所有消息按时间和ID降序排序，未做分组过滤，因此会返回全部行。要实现每个chat_id只保留最新消息条目，可采用以下方案：

方案一：使用窗口函数（推荐，适配支持窗口函数的数据库） #

借助ROW_NUMBER()窗口函数，按chat_id分组后，在每组内按created_at降序、id降序排序，仅保留每组的第一条数据：

SELECT id, chat_id, created_at, text
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY chat_id ORDER BY created_at DESC, id DESC) AS rn
    FROM message
) t
WHERE rn = 1;

说明： #

• PARTITION BY chat_id：将数据按chat_id分组
• ORDER BY created_at DESC, id DESC：每组内先按消息时间从新到旧排序，时间相同时按id降序（保证结果唯一）
• WHERE rn = 1：只保留每组排序后的第一条，即对应chat_id的最新消息

方案二：子查询关联（兼容不支持窗口函数的老版本数据库） #

如果你的数据库不支持窗口函数，可通过子查询找出每个chat_id的最大created_at及对应最大id，再关联原表获取完整消息数据：

SELECT m.*
FROM message m
INNER JOIN (
    SELECT 
        chat_id,
        MAX(created_at) AS max_created_at,
        MAX(id) AS max_id
    FROM message
    GROUP BY chat_id
) t ON m.chat_id = t.chat_id 
    AND m.created_at = t.max_created_at 
    AND m.id = t.max_id;

说明： #

• 子查询按chat_id分组，提取每组的最新时间和对应最大ID
• 通过关联原表，匹配到每个chat_id对应的最新消息行

以上两种方案均可得到你需要的结果：

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