gpt4 book ai didi

SQL - 即使项目的计数为零也返回所有行

转载 作者:行者123 更新时间:2023-12-02 19:25:41 25 4
gpt4 key购买 nike

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

下面是 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

当我执行此查询时,我得到以下结果:

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

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

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

我尝试过使用各种连接、IFNULL 和 ISNULL,但没有任何效果。

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

最佳答案

修改第二个查询

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 - 即使项目的计数为零也返回所有行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12747855/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com