SQL Query获取一行，以及关联行的计数

I have two tables, like this:

我有两个表,像这样:

#Articles:
ID | Title
1    "Article title"
2    "2nd article title"

#Comments:
ID | ParentID | Comment
1    1          "This is my comment"
2    1          "This is my other comment"

I've always wanted to know, what is the most elegant way to get the following result:

我一直想知道,获得以下结果的最优雅方法是什么:

ID | Title |          NumComments
1    "Article title"      2
2    "2nd article title"  0

This is for SQL Server.

这适用于SQL Server。

6 个解决方案

#1

This will normally be faster than the subquery approach, but as always you have to profile your system to be sure:

这通常比子查询方法更快,但一如既往,您必须对系统进行分析以确保:

SELECT a.ID, a.Title, COUNT(c.ID) AS NumComments
FROM Articles a
LEFT JOIN Comments c ON c.ParentID = a.ID
GROUP BY a.ID, a.Title

#2

select title, NumComments = (select count(*) 
from comments where parentID = id) from Articles

#3

SELECT 
   A.ID, A.Title, COUNT(C.ID) 
FROM 
   Articles AS A 
LEFT JOIN 
   Comments AS C ON C.ParentID = A.ID 
GROUP BY 
   A.ID, A.Title 
ORDER BY 
   A.ID

#4

SELECT Articles.Title, COUNT(Comments.ID) FROM Articles INNER JOIN Comments ON Articles.ID = Comments.ParentID GROUP BY Articles.Title

SELECT Articles.Title,COUNT(Comments.ID)FROM Articles INNER JOIN Comments ON Articles.ID = Comments.ParentID GROUP BY Articles.Title

#5

SELECT
Articles.ID
,Articles.TItle
,(SELECT Count(*) FROM Comments WHERE Comments.ParentId = Artices.ID) AS CommentCount
FROM Articles

#6

I'd do it like this:

我这样做:

select a.ID 'ArticleId',
       a.Title,
       count(c.ID) 'NumComments'
from   Articles a
left join
       Comments c
on     a.ID = c.ParentID
group by a.ID, a.Title

This might help in deciding between joining or using sub query:

这可能有助于决定加入或使用子查询:

Transact-SQL - sub query or left-join?

Transact-SQL - 子查询还是左连接?

#1