SQL - 即使项目的计数为零也返回所有行 [英] SQL - Returning all rows even if count is zero for item

问题描述

我正在根据日期范围进行计数.目前查询确实返回了正确的结果，但我需要其他信息.在当前形式中，查询显示具有正确计数的项目.但是，我需要显示所有项目，即使它们在指定日期范围内的计数为零.

I am performing a count based on a date range. Currently the query does return the correct result but I require additional information. In it's current form, the query shows the item with the correct count. However I need all items to be shown, even if their count is zero for the date range specified.

这里是SQL代码:

INSERT INTO @CreationCount (BaselineID, Name)

SELECT distinct [BaselineID],[Name] 
FROM [Baseline_INFO] 

DECLARE @ReqType TABLE (Type nvarchar(128))
INSERT INTO @ReqType (Type)
SELECT DISTINCT Tree.Type as 'Requirement Type'
FROM [TREE]
INNER JOIN [Project_INFO]  ON [Project_INFO].[ProjectID]=[Tree].[Project_ID] 
INNER JOIN [Baseline_INFO] ON [Baseline_INFO].[BaselineID]=[Tree].[Baseline_ID]
WHERE [Project_INFO].[Name] = 'Address Book' AND [Baseline_INFO].[Name] = 'Current
Baseline' 
Group By Tree.Type

SELECT Tree.Type as 'Requirement Type', COUNT(Tree.Type) as 'Number in Creation Range' 
FROM [Tree] 
INNER JOIN @ReqType As RT on RT.Type = Tree.Type
INNER JOIN [Project_INFO]  ON [Project_INFO].[ProjectID]=[Tree].[Project_ID] 
INNER JOIN @CreationCount AS CCount ON CCount.BaselineID=Tree.Baseline_ID 
WHERE [Project_INFO].[Name] = 'Address Book' AND CCount.Name = 'Current Baseline' 
AND [Tree].[creationDate] >= ('2010-01-01') and [Tree].[creationDate] < ('2020-01-01') 
GROUP BY tree.Type

当我执行这个查询时，我得到以下结果:

When I execute this query I get the following result:

https://dl.dropbox.com/u/17234826/SQLresult.png

这个结果是正确的，但是我需要列出所有需求类型，即使在创建范围内没有需求，即

This result is correct however I need all requirement types to be list, even if there are no requirements in the creation range, i.e.

https://dl.dropbox.com/u/17234826/SQLresult1.png

我尝试过使用各种连接、IFNULL 和 ISNULL，但我没有任何工作.

I have tried using various joins, IFNULL and ISNULL but I haven't got anything to work.

如果有人能指出我正确的方向，我将不胜感激.

If someone could point me in the right direction I'd appreciate it.

推荐答案

修改第二个查询

SELECT Tree.Type as 'Requirement Type',
       COUNT(CASE WHEN [Tree].[creationDate] >= ('2010-01-01') and [Tree].[creationDate] < ('2020-01-01') THEN Tree.Type END) AS 'Number in Creation Range'
FROM [Tree] 
INNER JOIN @ReqType As RT on RT.Type = Tree.Type
INNER JOIN [Project_INFO]  ON [Project_INFO].[ProjectID]=[Tree].[Project_ID] 
INNER JOIN @CreationCount AS CCount ON CCount.BaselineID=Tree.Baseline_ID 
WHERE [Project_INFO].[Name] = 'Address Book' AND CCount.Name = 'Current Baseline' 
GROUP BY tree.Type

这篇关于SQL - 即使项目的计数为零也返回所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！