如何在左联接中获取另一个表的计数 [英] How to get count of another table in a left join

问题描述

我有多个桌子

post
    id  Name
    1   post-name1
    2   post-name2

user
    id  username
    1   user1
    2   user2

post_user
    post_id   user_id
    1         1
    2         1

post_comments
    post_id   comment_id
    1         1
    1         2
    1         3

我正在使用这样的查询:

I am using a query like this:

SELECT post.id, post.title, user.id AS uid, username
FROM `post`
LEFT JOIN post_user ON post.id = post_user.post_id
LEFT JOIN user ON user.id = post_user.user_id
ORDER BY post_date DESC

它按预期工作.但是，我也希望获得每个帖子的评论数.因此，如何修改此查询，以便获得评论数.

It works as intended. However I would like the get the number of comments for each post too. So how can I modify the this query so I can get the count of comments.

有什么想法吗?

推荐答案

SELECT post.id, post.title, user.id AS uid, username, COALESCE(x.cnt,0) AS comment_count
FROM `post`
LEFT JOIN post_user ON post.id = post_user.post_id
LEFT JOIN user ON user.id = post_user.user_id
LEFT OUTER JOIN (SELECT post_id, count(*) cnt FROM post_comments GROUP BY post_id) x ON post.id = x.post_id
ORDER BY post_date DESC

在没有任何评论的情况下使其成为外部联接

made it an outer join in case there aren't any comments

将IsNull更改为Coalesce

这篇关于如何在左联接中获取另一个表的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！