结合3个从同一表中选择陈述到SQL中的1个表

Question

我正在尝试运行3个不同的在1个表上选择查询，我返回创建的计数，完成，未决基于 create_date 和 modified_date 的任务 我可以在上区分 与等待在 modified_date中基于字段状态

分别运行每个查询时返回正确的答案。

但是我想要的是将这3个查询组合在一个表中，如下所示：

+---------------------- +------------------+---------------------+
|  Total_created_files  | Total_done_files | Total_pending_files |
+-----------------------+------------------+---------------------+
|          14           |         40       |         9           | 
+-----------------------+------------------+---------------------+          

代码：

select 
count([create_date]) as Total_created_files
FROM [TEC_APP].[dbo].[case_to_do]
where  [create_date] >='2022-05-01 00:00:00.000'
AND
CAST([create_date] AS date) <=  CAST(GETDATE() AS date)

select 
count([modified_date]) as Total_done_files
FROM [TEC_APP].[dbo].[case_to_do]
where  [modified_date] >='2022-05-01 00:00:00.000'
AND
CAST([modified_date] AS date) <=  CAST(GETDATE() AS date)
AND
status = 'DONE'

select 
count([modified_date]) as Total_pending_files
FROM [TEC_APP].[dbo].[case_to_do]
where  [modified_date] >='2022-05-01 00:00:00.000'
AND
CAST([modified_date] AS date) <=  CAST(GETDATE() AS date)
AND
status = 'Pending'                                                  

Answer 1

根据需要，将条件聚合使用计数。我在这里做了几个假设，但这应该是您所追求的：

USE TEC_APP;
GO

SELECT COUNT(CASE WHEN [create_date] < DATEADD(DAY,1,CONVERT(date,GETDATE())) THEN 1 END) AS Total_created_files,
       COUNT(CASE WHEN [modified_date] >= '20220501' AND [modified_date] < DATEADD(DAY,1,CONVERT(date,GETDATE())) AND Status = 'DONE' THEN 1 END) AS Total_done_files,
       COUNT(CASE WHEN [modified_date] >= '20220501' AND [modified_date] < DATEADD(DAY,1,CONVERT(date,GETDATE())) AND Status = 'Pending' THEN 1 END) AS Total_pending_files
FROM [dbo].[case_to_do]
WHERE create_date >= '20220501'; --Presumably something can't be modified before it's created.

Answer 2

将条件聚合与案例表达式：

SELECT SUM(case when <First Condition here> then 1 else 0 end) as Total_created_files,
       SUM(case when <Second Condition here> then 1 else 0 end) as Total_done_files,
       SUM(case when <Third Condition here> then 1 else 0 end) as Total_pending_files
FROM [TEC_APP].[dbo].[case_to_do]