MySQL:同一个 LEFT JOIN 表中的多个计数不起作用

Question

我有这 3 个表:

1. posts (num, title, createdDate)
2. comments (num, post_num, parent_comment)
3. likes (comment_num)

我正在尝试查询以获得以下结果:

1. all posts
2. comments count including replies
3. replies count only (comments.parent_comment != 0)
4. total likes count
5. total participants in a post

到目前为止，除了#3，我对所有事情都很好，即只回复计数 (comments.parent_comment != 0)

这是查询:

SELECT 
posts.num, 
posts.title, 
DATEDIFF(NOW(),posts.createdDate) as NumOfDays,
COUNT( comments.num)  AS totalComments,
COUNT( CASE WHEN comments.parent_comment=0 THEN 0 ELSE comments.parent_comment END)  AS totalReplies,


COUNT( likes.comment_num  ) AS totalLikes,
COUNT( DISTINCT comments.member_num)  AS participants,
cms_uploads.urlPath

FROM posts
LEFT JOIN comments ON comments.post_num = posts.num
LEFT JOIN likes ON likes.comment_num = comments.num


GROUP BY posts.num
ORDER BY totalComments DESC

我得到的结果是 "totalReplies" 计数类似于 "totalComments" 计数。

有什么想法可以让我通过获得正确的总回复数来实现这一点吗？

谢谢!

Answer 1

COUNT() 将计算零。使用 SUM()，一个 la:

SUM( CASE WHEN comments.parent_comment = 0 THEN 1 ELSE 0 END)  AS totalReplies,