如何在mysql中离开联接多个一对多表? [英] How to left join multiple one to many tables in mysql?

问题描述

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