问题描述
我在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中离开联接多个一对多表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！