时间戳

首页 > 解决方案 > 从具有连接和空值的 4 个表中计数

mysql - 从具有连接和空值的 4 个表中计数

问题描述

表：

 Posts           Comments           Likes            Users
ID | User_id     ID | Post_id      ID | Post_id     ID | Name
-------          --------          --------         --------
1  | 101          1 | 1             1 | 3          101 | 'Michael'
2  | 101          2 | 3             2 | 3          202 | 'Daniel'
3  | 303          3 | 3             3 | 3          303 | 'Scott'

我期望查询的是：

ID | Total_comments | Total_likes | Name
1  |       1        |     0       | 'Michael'
2  |       0        |     0       | 'Michael'
3  |       2        |     3       | 'Scott'

然而，这就是我得到的：

ID | Total_comments | Total_likes | Name
1  |       1        |     0       | 'Michael'
2  |       0        |     0       | 'Michael'
3  |       6        |     6       | 'Scott'

使用此查询：

SELECT Posts.ID,
       COUNT(Comments.Post_id) as Total_comments, 
       COUNT(Likes.Post_id) as Total_likes,
       Users.Name
       FROM Posts INNER JOIN Users 
       ON Users.ID = Posts.User_id 
       LEFT JOIN Comments 
       ON Comments.Post_id = Posts.ID 
       LEFT JOIN Likes 
       ON Likes.Post_id = Posts.ID 
       GROUP BY Posts.ID

我想知道，上面的查询可以通过什么方式修改以返回预期的结果？什么是实现这一目标的好方法？

标签： mysqlsqldatabasecountleft-join

解决方案

您正在沿两个维度加入，因此您将获得笛卡尔积。这就是发生的事情。

一个 hacky 解决方案 - 如果给定帖子没有太多评论和喜欢，效果很好 - 是使用COUNT(DISTINCT)：

SELECT Posts.ID,
       COUNT(DISTINCT Comments.Post_id) as Total_comments, 
       COUNT(DISTINCT Likes.Post_id) as Total_likes,
       Users.Name

不过，最有效的解决方案可能是相关子查询：

SELECT p.ID,
       (SELECT COUNT(*) FROM Comments c WHERE c.Post_Id = p.ID) as Total_comments, 
       (SELECT COUNT(*) FROM Likes l WHERE l..Post_id = p.ID) as Total_likes,
       u.Name
FROM Posts p INNER JOIN
     Users u
     ON u.ID = p.User_id ;

这更快，因为它避免了外部聚合。comments(post_id)但是，它需要和上的索引likes(post_id)。

推荐阅读