没有返回任何行时，TSQL COUNT显示0 [英] TSQL COUNT show 0 when no row returned

问题描述

我有一张表，即 Documents (文档)，该表列出了所有文档以及有关它们的各种信息.对于一组选定的作者，我想要做的是获得他们在过去一年中创作的所有文档的数量.对于每个文档，我们都有一列来存储作者姓名和ID.

I have one table, Documents, that lists all documents and various information about them. What I'm trying to do is, for a select group of authors, get a count of all documents they've authored in the past year. For each document, we have a column to store the Author name and ID.

我现在可以通过下面的查询得到想要的东西，但是我的问题是我还需要列出所有未编写任何文档的作者. (因此，对于已签署的文档数列，它们的值为零).这是我现在所拥有的:

I am currently getting what I want with the query below, but my problem is I also need to list all authors that haven't authored any documents. (So, for the Number of Docs Signed column they'd have a zero value) Here's what I have now:

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

通过阅读全文，我知道我认为我需要某种子查询才能加入，以便在COUNT不返回任何行时显示"0".但是对于我的一生，我不知道该怎么做.我非常确定它必须是类似

From reading around on SO I know I think I need some kind of subquery that I can join in order to show a '0' when there are no rows returned by COUNT. But for the life of me I can't figure out how to do it. I'm pretty sure it needs to be something like this.

推荐答案

从Authors表开始，然后左向外部联接到documents表.这意味着您需要将条件移到外部联接中.

Start from the Authors table and do a left outer join to the documents table. This means you need to move the criteria into the outer join...

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

这篇关于没有返回任何行时，TSQL COUNT显示0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！