我在mysql中联接三个表时遇到问题.
i have a problem with joining three tables in mysql.
让我们说有一个名为posts的表,我将其保留在其中,有一个名为likes的表,其中存储了user_id和post_id,还有第三个名为comments的表,其中存储了user_id和post_id,并在其中评论文本.
lets say we have a table named posts which I keep my entries in it, i have a table named likes which i store user_id's and post_id's in and a third table named comments which i store user_id's and post_id's and comment's text in it.
我需要一个查询来获取我的条目列表,其中包含每个条目的喜欢和评论数.
I need a query that fetches list of my entries, with number of likes and comments for each entry.
我正在使用此查询:
SELECT posts.id, count(comments.id) as total_comments, count(likes.id) as total_likes FROM `posts` LEFT OUTER JOIN comments ON comments.post_id = posts.id LEFT OUTER JOIN likes ON likes.post_id = posts.id GROUP BY posts.id但是此查询存在问题,如果某项的评论为空,则喜欢"计数就可以了,但是如果某条目具有2条评论和4个赞",则total_comments和total_likes均为"8",这意味着mysql将它们相乘. 我很困惑,我不知道该怎么办.
but there is a problem with this query, if comments are empty for an item, likes count is just ok, but lets say if an entry has 2 comments and 4 likes, both total_comments and total_likes will be "8", meaning that mysql multiplies them. I'm confused and I dont know what whould I do.
谢谢你.
推荐答案使用count(distinct comments.id)和count(distinct likes.id),前提是这些ID是唯一的.
Use count(distinct comments.id) and count(distinct likes.id), provided these ids are unique.
更多推荐
如何在mysql中离开联接多个一对多表?
发布评论