MySQL如何在已有查询中统计每个主帖对应的回复数量

修改后的查询语句 #

直接在原有查询的SELECT部分新增回复统计列即可，兼容现有逻辑：

SELECT posts.id, posts.username, posts.time, cat.cat_name, 
posts.title, posts.content, posts.reply,
posts.user_file, posts.audio,
social.views, social.likes,
-- 新增回复数统计列
(SELECT COUNT(*) FROM posts AS reply_posts WHERE reply_posts.reply = posts.id) AS reply_count
FROM posts
LEFT JOIN user on posts.user_id = user.id 
LEFT JOIN cat ON posts.cat_id = cat.id
LEFT JOIN social ON posts.id = social.post_id
WHERE social.likes IN (SELECT social.likes FROM social 
WHERE social.id IN (SELECT MAX(social.id) 
FROM social GROUP BY post_id))
GROUP BY social.post_id
HAVING posts.reply = 0

逻辑说明 #

• 新增的reply_count列通过关联子查询实现：遍历每一条主帖数据时，统计posts表中所有reply字段等于当前主帖ID的记录总数，就是该主帖对应的回复数量。
• 子查询内给posts表设置了别名reply_posts，避免和主查询的主帖posts表产生命名冲突。
• 无回复的主帖对应的reply_count会自动返回0，无需额外处理。

大数据量优化方案 #

如果posts表数据量级较大，关联子查询性能偏低，可以用预聚合的方式提前统计所有主帖的回复数再关联：

SELECT posts.id, posts.username, posts.time, cat.cat_name, 
posts.title, posts.content, posts.reply,
posts.user_file, posts.audio,
social.views, social.likes,
IFNULL(reply_stat.reply_count, 0) AS reply_count
FROM posts
LEFT JOIN user on posts.user_id = user.id 
LEFT JOIN cat ON posts.cat_id = cat.id
LEFT JOIN social ON posts.id = social.post_id
LEFT JOIN (
    SELECT reply AS main_post_id, COUNT(*) AS reply_count
    FROM posts
    WHERE reply != 0
    GROUP BY reply
) AS reply_stat ON posts.id = reply_stat.main_post_id
WHERE social.likes IN (SELECT social.likes FROM social 
WHERE social.id IN (SELECT MAX(social.id) 
FROM social GROUP BY post_id))
GROUP BY social.post_id
HAVING posts.reply = 0

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