具有多个日期范围查询的查询

Question

我必须根据日期范围从一个表中收集数据。现在我正在使用多选查询来获取此计数Ex

Select  @count1 =  count(*) 
from table
where  createddate between @date1 and @date2 and status in (1,2)
Select  @count2 =  count(*) 
from table
where  createddate between @date2 and @date3 and status in (3,4)
Select @count23 =  count(*) 
from table
where  createddate between @date3 and @date4 and status in (5,6)
Select count1, count2, count3 

如何合并以上3个查询？它会提高性能吗？

Answer 1

使用条件聚合：

SELECT COUNT(CASE WHEN createdate BETWEEN @Date1 AND @Date2 AND [Status] IN (1,2) THEN 1 END) AS Count1,
COUNT(CASE WHEN createdate BETWEEN @Date2 AND @Date3 AND [Status] IN (3,4) THEN 1 END) AS Count2
FROM dbo.YourTable;