mysql - 如何编写具有两个左连接和两个计数的 MySql 查询

Question

我有三个表帖子，评论，喜欢，我想获取所有帖子以及他们的喜欢和评论数量我试过这个

SELECT posts.id,COUNT(comments.postId) as num_of_comments,COUNT(likes.postId) as num_of_likes                                                                     
FROM posts
LEFT JOIN comments ON comments.postId = posts.id
LEFT JOIN likes ON likes.postId = posts.id
GROUP BY posts.id

标签： mysqljoincount

Answer 1

您应该在按结果分组的子查询上使用左连接

select  posts.id, t1.num_of_comments, t2.num_of_likes
from posts
leftjoin (
  select comments.postId, COUNT(*) as num_of_comments
  from comments 
  group by comments.postId
) t1 on t1.postId = posts.id
leftjoin (
  select likes.postId, COUNT(*) as num_of_likes
  from likes
  group by likes.postId
) t2 on t2.postId = posts.id