sql-server - 多日期范围查询

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 个查询？它会提高性能吗？

标签： sql-serverdatabasesqlperformance

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;