GROUP BY结合ORDER BY问题：如何显示用户最后发帖时间？

我需要展示某主题下的作者列表，包含作者姓名及其在该主题下的最后发帖时间。由于用户可能多次发帖，我对poster_id设置了GROUP BY，同时希望按发帖时间倒序排列（最新在前），因此使用了maxtime DESC。但列表展示正常后，显示的并非用户最后发帖时间，而是首次发帖时间。

表结构 #

USERS表 #

POSTS表 #

当前SQL查询语句 #

SELECT 
    p.poster_id, 
    p.post_time, 
    p.post_id,   
    Max(p.post_time) AS maxtime, 
    u.user_id, 
    u.username,
FROM POSTS as p 
INNER JOIN USERS as u ON u.user_id = p.poster_id 
WHERE p.topic_id = 6 
GROUP BY p.poster_id 
ORDER BY maxtime DESC

问题原因 #

你的查询中，SELECT p.post_time会返回分组内任意一条记录的时间（不同数据库的行为可能不同，比如MySQL在非严格模式下会取分组后的第一条记录时间，也就是用户的首次发帖时间），而不是你需要的最大时间。虽然你用了MAX(p.post_time) AS maxtime，但同时选中的p.post_time和这个聚合值没有关联，导致显示错误。

解决方案 #

方案1：只获取作者及最后发帖时间（不需要帖子详情） #

调整SELECT字段，直接使用聚合后的MAX(p.post_time)作为最后发帖时间，同时规范GROUP BY子句（包含所有非聚合字段，避免数据库兼容性问题）：

SELECT 
    p.poster_id, 
    MAX(p.post_time) AS last_post_time, 
    u.user_id, 
    u.username
FROM POSTS as p 
INNER JOIN USERS as u ON u.user_id = p.poster_id 
WHERE p.topic_id = 6 
GROUP BY p.poster_id, u.user_id, u.username
ORDER BY last_post_time DESC

方案2：同时获取最后一条帖子的详情（如post_id） #

如果需要展示用户最后一条帖子的具体信息（比如post_id），可以先通过子查询找到每个作者在该主题下的最大发帖时间，再关联原表和用户表定位到对应的帖子：

SELECT 
    p.poster_id, 
    p.post_time AS last_post_time,
    p.post_id,
    u.user_id, 
    u.username
FROM POSTS as p
INNER JOIN USERS as u ON u.user_id = p.poster_id
INNER JOIN (
    SELECT poster_id, MAX(post_time) AS max_time
    FROM POSTS
    WHERE topic_id = 6
    GROUP BY poster_id
) AS latest_posts ON p.poster_id = latest_posts.poster_id AND p.post_time = latest_posts.max_time
WHERE p.topic_id = 6
ORDER BY p.post_time DESC

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