如何从表中加入COUNT，然后将该COUNT与另一个JOIN绑定 [英] How to JOIN a COUNT from a table, and then effect that COUNT with another JOIN

问题描述

我有三个表

发布

  b $ b 1'Something'
 2'其他'
 3'一个'
  

$ b b

评论

  ID PostId ProfileID评论
 1 1 1我的名字是'
 2 2 2'我喜欢蛋糕'
 3 3 3'我讨厌蛋糕'
  

  ID已核准
 1 1 
 2 0 
 3 1 
  

我想计算批准评论的个人资料的评论。

我可以从Post选择数据，然后从注释加入一个计数。

我希望的结果是

CommentCount

  PostId计数
 1 1 
 2 0 
 3 1 
 
解决方案
 div> 
您可以使用这样的嵌套选择：
  SELECT Post.Id，temp.Count 
 FROM Post 
 LEFT JOIN 
（SELECT Post.Id，COUNT（Comment.ID）AS Count 
 FROM Post 
 LEFT JOIN评论ON Comment.PostId = Post.ID 
 LEFT JOIN Profile ON Profile.ID = Comment.ProfileID 
 WHERE Profile.Approved = 1 
 GROUP BY Post.Id）
 temp ON temp.Id = Post.ID 
  
这将给你null没有帖子，而不是没有记录：
  1 1 
 2 null 
 3 1 
  
只是为了改进，你可以使用一个if来摆脱nulls。
  .Id，if（temp.Count> = 1，temp.Count，0）as newCount 
 FROM Post 
 LEFT JOIN 
（SELECT Post.Id，COUNT（Comment.ID）AS计数
 FROM Post 
 LEFT JOIN评论ON Comment.PostId = Post.ID 
 LEFT JOIN简介ON Profile.ID = Comment.ProfileID 
 WHERE Profile.Approved = 1 
 GROUP BY Post.Id）temp ON temp.Id = Post.ID 
  
最初想要的：
  1 1 
 2 0 
 3 1 
  
注意：尽管很可能是一个更优雅的解决方案!!!! 
 
I have three tables

Post
ID  Name
1   'Something'
2   'Something else'
3   'One more'
Comment
ID  PostId  ProfileID  Comment
1   1       1          'Hi my name is' 
2   2       2          'I like cakes'
3   3       3          'I hate cakes'
Profile
ID  Approved
1   1          
2   0          
3   1          
I want to count the comments for a post where the profile for the comment is approved

I can select the data from Post and then join a count from Comment fine. But this count should be dependent on if the Profile is approved or not.

The results I am expecting is 

CommentCount
PostId  Count
1       1
2       0
3       1
Thanks for any help.
解决方案
You could use a nested select like this:
SELECT Post.Id, temp.Count
FROM Post
LEFT JOIN
(SELECT Post.Id, COUNT(Comment.ID) AS Count
FROM Post
LEFT JOIN Comment ON Comment.PostId = Post.ID
LEFT JOIN Profile ON Profile.ID = Comment.ProfileID
WHERE Profile.Approved = 1
GROUP BY Post.Id)
temp ON temp.Id = Post.ID
Which would give you null where there are no posts, rather than no record:
1  1
2  null
3  1
Just to improve on that, you could use an if to get rid of the nulls
SELECT Post.Id, if(temp.Count >= 1,temp.Count,0) as newCount
FROM Post
LEFT JOIN
(SELECT Post.Id, COUNT(Comment.ID) AS Count
FROM Post
LEFT JOIN Comment ON Comment.PostId = Post.ID
LEFT JOIN Profile ON Profile.ID = Comment.ProfileID
WHERE Profile.Approved = 1
GROUP BY Post.Id) temp ON temp.Id = Post.ID
Which gives you what you originally wanted:
1  1
2  0
3  1
Note: There is most probably a more elegant solution though!!!!

                        
这篇关于如何从表中加入COUNT，然后将该COUNT与另一个JOIN绑定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！