SQL 如何从表中加入 COUNT，然后用另一个 JOIN 影响该 COUNT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2613005/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to JOIN a COUNT from a table, and then effect that COUNT with another JOIN
提问by Jake N
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.
我可以从 Post 中选择数据，然后从 Comment Fine 中加入一个计数。但是这个计数应该取决于配置文件是否被批准。
The results I am expecting is
我期待的结果是
CommentCount
评论数
PostId Count
1 1
2 0
3 1
回答by theandywaite
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:
在没有帖子而不是没有记录的情况下，这会给您 null ：
1 1
2 null
3 1
Just to improve on that, you could use an if to get rid of the nulls
只是为了改进这一点，您可以使用 if 来摆脱空值
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!!!!
注意：虽然很可能有一个更优雅的解决方案！！！！
回答by topchef
From the definition of the COUNT function:
从 COUNT 函数的定义来看：
The COUNT function will only count those records in which the field in the brackets is NOT NULL.
COUNT 函数只会对括号中的字段为 NOT NULL 的记录进行计数。
This means that simple outer join like this would work:
这意味着像这样的简单外连接可以工作：
SELECT Post.ID, COUNT(Comment.ID)
FROM Post LEFT JOIN Comment ON (Post.ID = Comment.PostId)
LEFT JOIN Profile ON (Profile.ID = Comment.ProfileID AND
Profile.Approved = 1)
GROUP BY Post.ID
回答by Alex Bagnolini
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
Probably you didn't paste it for the sake of the example, but you might evaluate to de-normalize the Profiletable together with the Commentone, by moving the Approvedcolumn in it.
可能您不是为了示例而粘贴它，但您可能会通过移动其中的列来评估将Profile表格与表格一起反规范化。CommentApproved
