sql - left join - count

问题:

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)

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.

回答1:

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 ARTICLES table, you'll have to define in the GROUP BY clause because they aren't having an aggregate function performed on them.

回答2:

This should be more efficient because the group by is only done on the Comment table.

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

回答3:

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

回答4:

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

回答5:

    -- 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;