SQL Server 获取多选查询的总数并作为单个表返回

zz2j4svz 于 2022-12-17 发布在其他

关注(0)|答案(2)|浏览(113)

我想获得以下四个单独SQL查询的总数，并作为单个表返回，如下所示：

select count(*) OpenTaskCount from [COM].[USER_GENERATED_TASK_LIST] where TASK_STATUS = 'Active' and ACTIVE_FLAG = 1     
select count(*) AnsweredTaskCount from [COM].[USER_GENERATED_TASK_LIST] where TASK_STATUS = 'Active' and ACTIVE_FLAG = 0     
select count(*) CandidateTaskCount from [COM].[USER_GENERATED_TASK_LIST] where TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE())    
select count(*) AlertTaskCount from [COM].[USER_GENERATED_TASK_LIST] where TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE())

有没有简单的方法从上面的选择查询中获取总任务，然后作为单行返回。

sql-server

来源：https://stackoverflow.com/questions/74797783/get-total-count-for-the-multiple-select-queries-and-return-as-single-table

2条答案

按热度按时间

yhxst69z1#

您可以使用条件式汇总，例如：

select
    Sum(case when ACTIVE_FLAG = 1 then 1 else 0 end) OpenTaskCount,
    Sum(case when ACTIVE_FLAG = 0 then 1 else 0 end) AnsweredTaskCount,
    Sum(case when ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE()) then 1 else 0 end) CandidateTaskCount,
    Sum(case when ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE()) then 1 else 0 end) AlertTaskCount
from COM.USER_GENERATED_TASK_LIST
where TASK_STATUS = 'Active';

编辑-要添加总计列，我可能只使用派生表，例如：

select OpenTaskCount, AnsweredTaskCount, CandidateTaskCount, AlertTaskCount,
  OpenTaskCount + AnsweredTaskCount + CandidateTaskCount + AlertTaskCount as TotalTask
from (
  select 
    Sum(case when ACTIVE_FLAG = 1 then 1 else 0 end) OpenTaskCount,
    Sum(case when ACTIVE_FLAG = 0 then 1 else 0 end) AnsweredTaskCount,
    Sum(case when ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE()) then 1 else 0 end) CandidateTaskCount,
    Sum(case when ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE()) then 1 else 0 end) AlertTaskCount
  from COM.USER_GENERATED_TASK_LIST
  where TASK_STATUS = 'Active'
)t;

赞(0）回复(0）举报 2022-12-17

svgewumm2#

您可以尝试使用条件聚合和适当的CASE表达式（聚合将消除NULL值）：

SELECT 
   OpenTaskCount = COUNT(CASE WHEN TASK_STATUS = 'Active' AND ACTIVE_FLAG = 1 THEN 1 END),
   AnsweredTaskCount = COUNT(CASE WHEN TASK_STATUS = 'Active' and ACTIVE_FLAG = 0 THEN 1 END),
   CandidateTaskCount = COUNT(CASE WHEN TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE()) THEN 1 END),
   AlertTaskCount = COUNT(CASE WHEN TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE()) THEN 1 END)
   TotalTask = COUNT(CASE WHEN
     (TASK_STATUS = 'Active' AND ACTIVE_FLAG = 1) OR        
     (TASK_STATUS = 'Active' and ACTIVE_FLAG = 0) OR
     (TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE())) OR
     (TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE()))
                     THEN 1 END)
FROM [COM].[USER_GENERATED_TASK_LIST]

赞(0）回复(0）举报 2022-12-17

我来回答

SQL Server 获取多选查询的总数并作为单个表返回

2条答案

相关问题

热门标签

最新问答