SQL ORDER BY与GROUP BY查询无法获取分组最新message问题

你当前写法拿不到对应最新时间的message，本质是SQL聚合逻辑规则导致的：

• MAX(chats.created_at)只会单独计算每个分组下的最大时间值，不会自动将同组其他字段关联到最大时间对应的那一行记录
• 如果你使用的是MySQL且未开启ONLY_FULL_GROUP_BY严格模式，SELECT列表中未参与GROUP BY、也未加聚合函数的字段，会直接返回分组内扫描到的第一行数据，这就是你始终拿到最早message的原因

方案1：窗口函数写法（支持MySQL 8.0+、PostgreSQL等主流新数据库，推荐使用） #

不需要先分组再关联，用窗口函数直接在分组内排序、计算聚合值，逻辑最清晰：

WITH ranked_chats AS (
  SELECT
    chat_title,
    chat_object,
    created_at,
    id,
    group_id,
    message,
    SUM(IF(admin_read = 0, 1, 0)) OVER (PARTITION BY chat_object) AS unread,
    ROW_NUMBER() OVER (PARTITION BY chat_object ORDER BY created_at DESC, id DESC) AS rn
  FROM chats
  WHERE group_id = 82
)
SELECT
  chat_title,
  chat_object,
  created_at AS latest,
  unread,
  id,
  group_id,
  message
FROM ranked_chats
WHERE rn = 1
ORDER BY latest

逻辑说明：

• 用ROW_NUMBER()给每个chat_object分组内的记录按创建时间倒序、主键id倒序编号，每个分组最新的一条记录编号固定为1
• 用SUM() OVER()窗口函数直接计算每个分组的未读消息数，不需要额外分组聚合
• 最后过滤rn=1就能直接拿到每个分组最新的整条记录，不会出现字段和时间不匹配的问题

方案2：子查询关联写法（兼容MySQL 5.x等不支持窗口函数的旧版本） #

先在子查询中算出每个分组的聚合值（最新时间、未读数），再关联原表匹配到最新时间对应的完整记录：

SELECT
  c.chat_title,
  c.chat_object,
  c.created_at AS latest,
  agg.unread,
  c.id,
  c.group_id,
  c.message
FROM chats c
INNER JOIN (
  SELECT
    chat_object,
    MAX(created_at) AS max_created_at,
    SUM(IF(admin_read = 0, 1, 0)) AS unread
  FROM chats
  WHERE group_id = 82
  GROUP BY chat_object
) agg 
  ON c.chat_object = agg.chat_object 
  AND c.created_at = agg.max_created_at
WHERE c.group_id = 82
ORDER BY latest

注意：如果同一个chat_object分组下存在多条created_at完全相同的记录，这个写法会返回多条匹配结果，可以把关联条件调整为匹配分组内最新的主键id，保证只返回唯一一条最新记录。

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