sql - 当没有行返回时，TSQL COUNT 显示 0

Question

我有一个表，Documents，它列出了所有文档和关于它们的各种信息。我想要做的是，对于一组选定的作者，计算他们在过去一年中创作的所有文档。对于每个文档，我们都有一列来存储作者姓名和 ID。

我目前通过下面的查询获得了我想要的结果，但我的问题是我还需要列出所有尚未创作任何文档的作者。 (因此，对于 Number of Docs Signed 列，它们的值为零)这是我现在拥有的:

SELECT 
[AuthorID] As "Author ID",
RTRIM([AuthorFirstName]) + ' ' + RTRIM([AuthorLastName]) AS "Author", 
COUNT(Document.ID) AS "Number of Docs Authored"

FROM Document

WHERE   [CompletedStatus] = 'Yes' 
AND     [AuthorID] IN (<list of author ID's>) 
AND     [CompletedOn] >= DATEADD(d, -365, getdate())


GROUP BY [AuthorID], [AuthorFirstName], [AuthorLastName]

ORDER BY [Number of Docs Signed] DESC

通过阅读 SO 我知道我认为我需要某种可以加入的子查询，以便在 COUNT 没有返回行时显示“0”。但是对于我的一生，我不知道该怎么做。我很确定它需要像 this 这样的东西.

Answer 1

从 Authors 表开始，对 documents 表进行左外连接。这意味着您需要将条件移动到外部连接中...

SELECT 
[AuthorID] As "Author ID",
RTRIM([AuthorFirstName]) + ' ' + RTRIM([AuthorLastName]) AS "Author", 
COUNT(Document.ID) AS "Number of Docs Authored"

FROM 
    Author a
    LEFT OUTER JOIN Document d on d.AuthorID = a.ID
        AND [CompletedStatus] = 'Yes' 
        AND [CompletedOn] >= DATEADD(d, -365, getdate())
WHERE
    a.ID IN (<list of author ID's>) 

GROUP BY a.[ID], [AuthorFirstName], [AuthorLastName]

ORDER BY [Number of Docs Signed] DESC