如何在结果集表中获取两个计数列 [英] How to get two counts columns in result set table
问题描述
我写sql查询时很差,请帮我把两列作为结果集如
OpenIssues ClosedIssues
50 1
通过以下查询但我得到的行如
OpenIssues
50
1
查询为如下:
选择 COUNT(*) as OpenIssues 来自 HX_Issue 其中 ProjectId = 1 和 ComponentId = 13 和 CreatedOn ' 2012/12/24' 和 ' 2013/01/30' 和 StatusId!= 23
union all
选择 COUNT(*) 关闭来自 HX_Issue 其中 ProjectId = 1 和 ComponentId = 13 和 CreatedOn ' 2012/12/24' 和 ' 2013/01/30' 和 StatusId = 23
选择
(选择 COUNT(*)来自 HX_Issue 其中 ProjectId = 1 和 ComponentId = 13 和 CreatedOn ' 2012/12/24' 和 ' 2013/01/30' 和 StatusId!= 23) as OpenIssues
,
(选择 COUNT(*)来自 HX_Issue 其中 ProjectId = 1 和 ComponentId = 13 和 CreatedOn ' 2012/12/24 ' 和 ' 2013/01/30 ' 和 StatusId = 23) as 关闭
更新:根据新要求
< pre lang =sql> 选择
distinct DATEPART(month,exq.CreatedOn)
(选择 COUNT(*)来自 HX_Issue inq 其中 inq.ProjectId = 1 和 inq.ComponentId = 13 和 DATEPART(月,inq .CreatedOn)= DATEPART(月,exq.CreatedOn)和 inq.CreatedOn ' 2012/12/24' 和 ' 2013/01/30' 和 StatusId!= 23) OpenIssues,
(选择 COUNT (*)来自 HX_Issue inq 其中 inq.ProjectId = 1 和 inq.ComponentId = 13 和 DATEPART(月,inq.CreatedOn)= DATEPART(月,exq.CreatedOn)inq.CreatedOn 介于 ' 2012/12/24' 和 ' 2013/01/30' 和 StatusId = 23) as ClosedIssues,
来自 HX_Issue exq
其中 ProjectId = 1 和 ComponentId = 13 和 exq.CreatedOn ' 2012/12/24' 和 ' 2013/01/30'
甚至更好:
选择end ) as OpenIssues,
DATEPART(月,CreatedOn)为月,
COUNT(案例 何时 StatusId!= 23 然后 1 else < span class =code-keyword> null
COUNT( case 当 StatusId = 23 然后 1 else null end ) as ClosedIssues
来自 HX_Issue其中 ProjectId = 1 和 ComponentId = 13
和 CreatedOn ' 2012/12/24 ' 和 ' 2013/01/30 '
group 按 DATEPART(month,CreatedOn)
Update2:基于更新的要求
< span class =code-keyword> SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- <温泉n class =code-comment> =========================================== ====
- 作者:ZoltánZörgő
- 创建日期:2013.01.31
- ================================== ===========
ALTER PROCEDURE [ dbo]。[IssueReport]
@ ProjectId int ,
@ ComponentId int ,
@ FromDate 日期,
@ ToDate 日期
AS
BEGIN
SET NOCOUNT ON ;
DateCte as
(
select cast( @ FromDate as datetime )DateValue
union all
选择 DateValue + 1
来自 DateCte
其中 DateValue + 1 < = @ ToDate
)
选择
DATEPART(年,日期值)为年,
DATEPART(月,DateValue)为月,
(选择 COUNT(*)来自 HX_Issue 其中 ProjectId = @ ProjectId 和 ComponentId = @ ComponentId 和 DATEPART(月,CreatedOn)= DATEPART(月,DateValue)和 DATEPART(年,CreatedOn)= DATEPART(年,DateValue)和 StatusId!= 23) OpenIssues,
(选择 COUNT(*)来自 HX_Issue 其中 ProjectId = @ProjectId 和 ComponentId = @ ComponentId 和 DATEPART(month,CreatedOn)= DATEPART(month,DateValue)< span class =code-keyword>和 DATEPART(year,CreatedOn)= DATEPART(年,DateValue)和 StatusId = 23) as ClosedIssues
来自 DateCte
group 按 DATEPART(年,日期值),DATEPART(月, DateValue)
OPTION (MAXRECURSION 0 )
结束
以下是使用示例:
EXEC [dbo]。[IssueReport]
@ ProjectId = 1 ,
@ ComponentId = 13 ,
@ FromDate = ' 2012.01.01' ,
@ ToDate = ' 2013.02.15'
GO
Hi,
I am very poor in writing sql queryies please help me to get two columns as a result set like
OpenIssues ClosedIssues
50 1
By the following query but i am getting in rows like
OpenIssues
50
1
The query as follows :
select COUNT(*) as OpenIssues from HX_Issue where ProjectId=1 and ComponentId=13 and CreatedOn between '2012/12/24' and '2013/01/30' and StatusId !=23
union all
select COUNT(*) as closed from HX_Issue where ProjectId=1 and ComponentId=13 and CreatedOn between '2012/12/24' and '2013/01/30' and StatusId =23
select (select COUNT(*) from HX_Issue where ProjectId=1 and ComponentId=13 and CreatedOn between '2012/12/24' and '2013/01/30' and StatusId !=23) as OpenIssues , (select COUNT(*) from HX_Issue where ProjectId=1 and ComponentId=13 and CreatedOn between '2012/12/24' and '2013/01/30' and StatusId =23) as closed
Update: based on "new requirement"
select distinct DATEPART(month, exq.CreatedOn) (select COUNT(*) from HX_Issue inq where inq.ProjectId=1 and inq.ComponentId=13 and DATEPART(month, inq.CreatedOn)=DATEPART(month, exq.CreatedOn) and inq.CreatedOn between '2012/12/24' and '2013/01/30' and StatusId !=23) as OpenIssues, (select COUNT(*) from HX_Issue inq where inq.ProjectId=1 and inq.ComponentId=13 and DATEPART(month, inq.CreatedOn)=DATEPART(month, exq.CreatedOn) inq.CreatedOn between '2012/12/24' and '2013/01/30' and StatusId =23) as ClosedIssues, from HX_Issue exq where ProjectId=1 and ComponentId=13 and exq.CreatedOn between '2012/12/24' and '2013/01/30'
Or even better:
select DATEPART(month, CreatedOn) as month, COUNT(case when StatusId!=23 then 1 else null end) as OpenIssues, COUNT(case when StatusId=23 then 1 else null end) as ClosedIssues from HX_Issue where ProjectId=1 and ComponentId=13 and CreatedOn between '2012/12/24' and '2013/01/30' group by DATEPART(month, CreatedOn)
Update2: based on even newer requirements
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Zoltán Zörgő -- Create date: 2013.01.31 -- ============================================= ALTER PROCEDURE [dbo].[IssueReport] @ProjectId int, @ComponentId int, @FromDate Date, @ToDate Date AS BEGIN SET NOCOUNT ON; with DateCte as ( select cast(@FromDate as datetime) DateValue union all select DateValue + 1 from DateCte where DateValue + 1 <= @ToDate ) select DATEPART(year, DateValue) as year, DATEPART(month, DateValue) as month, (select COUNT(*) from HX_Issue where ProjectId=@ProjectId and ComponentId=@ComponentId and DATEPART(month, CreatedOn)=DATEPART(month, DateValue) and DATEPART(year, CreatedOn)=DATEPART(year, DateValue) and StatusId !=23) as OpenIssues, (select COUNT(*) from HX_Issue where ProjectId=@ProjectId and ComponentId=@ComponentId and DATEPART(month, CreatedOn)=DATEPART(month, DateValue) and DATEPART(year, CreatedOn)=DATEPART(year, DateValue) and StatusId =23) as ClosedIssues from DateCte group by DATEPART(year, DateValue), DATEPART(month, DateValue) OPTION (MAXRECURSION 0) END
And here is the usage sample:
EXEC [dbo].[IssueReport] @ProjectId = 1, @ComponentId = 13, @FromDate = '2012.01.01', @ToDate = '2013.02.15' GO
这篇关于如何在结果集表中获取两个计数列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!