如何在MySQL中查询每个帖子的总评论数及最新一条评论内容

问题原因 #

你当前的SQL语句分组直接取comment字段时，数据库会默认返回分组内第一条命中的记录，不会自动按时间排序取最新值，所以才会出现post_id=3取到最早评论的问题。

解决方案 #

方案1：MySQL 8.0及以上版本（支持窗口函数）

WITH ranked_comments AS (
  SELECT 
    post_id,
    comment,
    -- 按帖子分组，评论时间倒序排序，最新的排第一
    ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY created_at DESC) AS comment_rank,
    -- 统计每个帖子的总评论数
    COUNT(*) OVER (PARTITION BY post_id) AS total_comments
  FROM comments
)
SELECT post_id, comment AS latest_comment, total_comments
FROM ranked_comments
WHERE comment_rank = 1 -- 只取每个分组排名第一的最新评论
-- 按post_id倒序排序和你给出的预期输出顺序一致，不需要可以删掉
ORDER BY post_id DESC;

方案2：低版本MySQL（不支持窗口函数）

SELECT 
  c.post_id,
  c.comment AS latest_comment,
  cnt.total_comments
FROM comments c
INNER JOIN (
  -- 先计算每个帖子的总评论数和最新评论的时间
  SELECT post_id, COUNT(*) AS total_comments, MAX(created_at) AS latest_comment_time
  FROM comments
  GROUP BY post_id
) cnt 
ON c.post_id = cnt.post_id AND c.created_at = cnt.latest_comment_time
ORDER BY c.post_id DESC;

注意事项 #

如果存在同一个帖子同一时间有多条评论的情况，上述语句会返回多条该帖子的记录，如果你只需要保留任意一条，可以在查询末尾加GROUP BY c.post_id即可。
另外你原SQL中关联posts表属于多余操作，你需要的字段都在comments表中即可完成查询，如果需要带出帖子的其他信息，可以自行添加关联逻辑。

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