MySQL如何重组同表GROUP BY结果 统计未读数取分组最新消息

表结构与需求说明 #

Chats表字段定义：

• ID：int类型，消息唯一标识
• CHAT_GROUP_ID：varchar类型，存储UUID格式的聊天组ID
• MESSAGE：字符串类型，存储聊天消息正文
• READ：数值类型，消息已读状态标记，0代表未读
• CREATED_AT：时间类型，存储消息发送时间

查询要求：

• 按CHAT_GROUP_ID对所有记录分组
• 每个分组统计未读消息总数，统计规则为SUM(IF(read = 0, 1, 0))
• 每个分组仅返回组内发送时间最新的1条消息内容

推荐写法（窗口函数版，兼容MySQL8.0+、PostgreSQL、SQL Server等主流数据库新版本） #

用窗口函数先给同组消息按发送时间倒序排号，再做聚合，逻辑清晰且性能优于子查询关联写法：

WITH sorted_chats AS (
    SELECT
        CHAT_GROUP_ID,
        MESSAGE,
        READ,
        ROW_NUMBER() OVER (
            PARTITION BY CHAT_GROUP_ID
            ORDER BY CREATED_AT DESC, ID DESC
        ) AS sort_rank
    FROM Chats
)
SELECT
    CHAT_GROUP_ID,
    SUM(IF(READ = 0, 1, 0)) AS unread_total,
    MAX(IF(sort_rank = 1, MESSAGE, NULL)) AS latest_message
FROM sorted_chats
GROUP BY CHAT_GROUP_ID

排序时加上ID DESC是为了处理同一时间戳下多条消息的冲突，保证取到的是最后插入的最新消息；每个分组内sort_rank=1的记录唯一，用MAX()取值不会出现结果偏差。

兼容旧版本写法（支持MySQL5.x等无窗口函数的数据库） #

先聚合算出每个聊天组的未读数、最新消息的时间与ID，再关联原表取对应消息内容：

SELECT
    base.CHAT_GROUP_ID,
    stat.unread_total,
    base.MESSAGE AS latest_message
FROM Chats base
INNER JOIN (
    SELECT
        CHAT_GROUP_ID,
        SUM(IF(READ = 0, 1, 0)) AS unread_total,
        MAX(CREATED_AT) AS latest_time,
        MAX(ID) AS latest_id
    FROM Chats
    GROUP BY CHAT_GROUP_ID
) stat
ON base.CHAT_GROUP_ID = stat.CHAT_GROUP_ID
AND base.CREATED_AT = stat.latest_time
AND base.ID = stat.latest_id

避坑提示 #

不要直接在GROUP BY语句中直接查询MESSAGE字段：

• 若数据库开启ONLY_FULL_GROUP_BY模式会直接抛出语法错误
• 模式关闭时会随机返回组内任意一条消息，无法保证取到最新内容
• 若业务有过滤已删除消息的需求，在所有查询Chats表的位置统一加上过滤条件即可

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