mysql - 来自许多具有条件的 INNER JOIN 表的多个计数

Question

我在弄清楚如何编写此查询时遇到了很多麻烦。这是数据集的示例以及我需要查询的内容:

**System Table**    

SystemID   Active
1          T
2          T
3          T
4          F
5          F
6          F

**BlogPost Table**      

BlogPostID  SystemID    Create_Month
100         2           Jan
101         2           Jan
102         2           Feb
103         3           Feb
104         3           Mar
105         6           Mar
106         6           Mar

**Comment Table**       

Comment ID  BlogPostID  Liked
201         100         T
202         100         T
203         100         T
204         102         T
205         102         T
206         102         T
207         103         F

所以，换句话说，我试图获得:按月显示在该月创建帖子的所有活跃系统、他们发布的帖子总数以及这些帖子的子集数谁有这样的评论。

最终结果是这样的:

Column 1 - Month

Column 2 - Count of Active Systems where a Post Created in Month

Column 3 - Count of Posts Applicable to those systems

Column 4 - Count of Applicable Posts that had comments that were liked  

我什至不知道从哪里开始。我可怕的“这显然是错误的”尝试如下。非常感谢任何帮助，谢谢!

SELECT
  Month, 
  COUNT(DISTINCT system.systemid), 
  COUNT(blogpost.BlogPostID)
  COUNT(comments.commentiD)
FROM
  system INNER JOIN
  blogpost ON system.systemid = blogpost.systemid INNER JOIN
  comments ON blogpost.BlogPostID = comment.BlogPostID
WHERE
  system.active = T 
  AND comments.like = T
GROUP BY month

Answer 1

一个复杂的!

SELECT 
b.Create_Month, 
COUNT(DISTINCT s.SystemID) as SystemCount, 
COUNT(DISTINCT b.BlogPostID) as PostsCount,
COUNT(DISTINCT t.BlogPostID) as PostsWithLike

FROM System s 

JOIN BlogPost b 
ON s.systemID = b.systemID 
AND s.Active = 'T'  

LEFT JOIN Comment c 
ON b.BlogPostID = c.BlogPostID

LEFT JOIN
(
SELECT DISTINCT c.BlogPostID as BlogPostID
FROM 
Comment c 
GROUP BY c.BlogPostID 
HAVING SUM(if(c.Liked='T',1,0))>0
) as t
ON b.BlogPostID = t.BlogPostID

GROUP BY b.Create_Month