sql - 左连接 - 计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2215754/
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
sql - left join - count
提问by TPR
suppose i have two tables. articles and comments.
假设我有两张桌子。文章和评论。
when i am selecting columns from articles table, i also want to select the number of comments on the article in the same select statement... (suppose the common field between these two tables is articleid)
当我从文章表中选择列时，我还想在同一个选择语句中选择对文章的评论数......（假设这两个表之间的公共字段是 articleid）
how do I do that? I can get it done, but I do not know if my way would be efficient, so i want to learn the right way.
我怎么做？我可以完成它，但我不知道我的方法是否有效，所以我想学习正确的方法。
采纳答案by OMG Ponies
Use:
用：
SELECT a.articleid,
COUNT(*) AS num_comments
FROM ARTICLES a
LEFT JOIN COMMENTS c ON c.articleid = a.articleid
GROUP BY a.articleid
Whatever columns you want from the ARTICLEStable, you'll have to define in the GROUP BYclause because they aren't having an aggregate function performed on them.
无论您想从ARTICLES表中获取什么列，您都必须在GROUP BY子句中进行定义，因为它们没有对它们执行聚合函数。
回答by JBrooks
This should be more efficient because the group by is only done on the Comment table.
这应该更有效，因为 group by 只在 Comment 表上完成。
SELECT
a.ArticleID,
a.Article,
isnull(c.Cnt, 0) as Cnt
FROM Article a
LEFT JOIN
(SELECT c.ArticleID, count(1) Cnt
FROM Comment c
GROUP BY c.ArticleID) as c
ON c.ArticleID=a.ArticleID
ORDER BY 1
回答by Gabriele Petrioli
This should do it..
这个应该可以。。
SELECT
article_column_1, article_column_2, count( ct.articleid) as comments
FROM
article_table at
LEFT OUTER JOIN comment_table ct ON at.articleid = ct.articleid
GROUP BY
article_column_1, article_column_2
回答by JonH
SELECT
a.Article,
a.ArticleID,
t.COUNTOFCOMMENTS
FROM
Article a
LEFT JOIN
Comment c
ON c.ArticleID=a.ArticleID
LEFT JOIN
(SELECT ArticleID, COUNT(CommentID) AS COUNTOFCOMMENTS FROM Comments GROUP BY ArticleID) t
ON t.ArticleID = a.ArticleID
回答by wwmbes
-- Working Syntax example from my environment changed to fit this context.
SELECT a.article
,A.articleid
,(
SELECT Count(B.articleid)
FROM dbo.comment AS B
WHERE A.articleid = B.articleid
) AS comment#
,(
SELECT Count(C.articleid)
FROM dbo.comments AS C
WHERE A.articleid = C.articleid
) AS comments#
FROM dbo.article AS A;
