MySQL8执行聚合查询后如何获取对应id 无需修改sql模式的实现方法

方案1：使用窗口函数（MySQL 8.0+ 推荐方案） #

MySQL 8.0原生支持窗口函数，直接用ROW_NUMBER()就能实现需求，完全符合ONLY_FULL_GROUP_BY的语法规范，不需要修改SQL模式：

WITH ranked_comments AS (
    SELECT 
        id,
        post_id,
        reply_count,
        ROW_NUMBER() OVER(
            PARTITION BY post_id 
            ORDER BY reply_count DESC, id ASC
        ) AS row_rank
    FROM Comment
)
SELECT id, post_id, reply_count 
FROM ranked_comments 
WHERE row_rank = 1;

逻辑说明：

• PARTITION BY post_id将所有记录按帖子ID分组
• 同组内先按reply_count倒序排序，保证回复数最高的记录排在最前
• 相同最高回复数的记录按id升序排序，保证id最小的排在前面
• 最后筛选每组排名第一的记录即可

如果不想用CTE写法，也可以嵌套子查询实现：

SELECT id, post_id, reply_count
FROM (
    SELECT 
        id,
        post_id,
        reply_count,
        ROW_NUMBER() OVER(PARTITION BY post_id ORDER BY reply_count DESC, id ASC) AS row_rank
    FROM Comment
) AS t
WHERE row_rank = 1;

方案2：关联聚合查询（兼容低版本MySQL） #

如果你需要兼容更低的MySQL版本，也可以用子查询先拿到每个帖子的最高回复数，再关联回原表取最小id：

SELECT MIN(c.id) AS id, c.post_id, c.reply_count
FROM Comment AS c
INNER JOIN (
    SELECT post_id, MAX(reply_count) AS max_reply
    FROM Comment
    GROUP BY post_id
) AS max_record
ON c.post_id = max_record.post_id AND c.reply_count = max_record.max_reply
GROUP BY c.post_id, c.reply_count;

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