社交平台帖子评论数统计SQL查询异常求助

开发社交平台时，需要在用户展开评论区前显示每个帖子的评论数。现有messages.dev表存储帖子，related_id字段标识该消息是否为某父帖子的评论（父帖子的related_id为null，评论的related_id对应父帖子的id）。

尝试通过子查询获取每个父帖的评论数，再与原表左连接添加num_ans字段，但遇到两个问题：

• 主查询被迫添加GROUP BY子句；
• 查询返回的num_ans值除了无评论的帖子显示0外，其余均为1，不符合实际评论数。

原SQL代码：

SELECT a.*, b.count as num_ans
FROM "messages.dev" a 
left outer join 
(
  select related_id, count(*) as num_ans
from "messages.dev"
where related_id is not null
group by related_id
order by num_ans desc
) as b
on a.id=b.related_id 
where a.related_id is null
-- don't understand this group by
group by a.id, a.created_at, a.related_id, a.author, a.content, a.num_like, a.num_impr, a.share_id
--
order by num_ans desc, created_at

核心错误点 #

1. 字段别名引用错误：子查询中已将count(*)命名为num_ans，但主查询错误地用b.count引用该字段——子查询返回的列中不存在count，这会导致数据库无法正确取值，最终返回异常的1。
2. 冗余的GROUP BY子句：左连接后每个父帖仅对应子查询中的一行数据（要么是评论数，要么是null），额外的分组操作会强制聚合数据，破坏关联结果，导致评论数显示异常。

修复后的SQL代码 #

SELECT a.*, COALESCE(b.num_ans, 0) as num_ans
FROM "messages.dev" a 
LEFT OUTER JOIN 
(
  SELECT related_id, COUNT(*) as num_ans
  FROM "messages.dev"
  WHERE related_id IS NOT NULL
  GROUP BY related_id
) as b
ON a.id = b.related_id 
WHERE a.related_id IS NULL
ORDER BY num_ans DESC, created_at

额外优化说明 #

• 移除子查询中的ORDER BY：子查询作为连接数据源，内部排序不影响最终结果，反而增加性能开销；
• 用COALESCE处理null：确保无评论的帖子num_ans显示为0，避免返回null值，更贴合业务需求。

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