如何在SQL Server中合并时间间隔

您可以使用递归CTE构建日期列表，然后计算不同的日期。

declare @T table(  startDate date,  endDate date);insert into @T values('2011-01-01', '2011-01-05'),('2011-01-04', '2011-01-08'),('2011-01-11', '2011-01-15');with C as(  select startDate,         endDate  from @T  union all  select dateadd(day, 1, startDate),         endDate  from C  where dateadd(day, 1, startDate) < endDate       )select count(distinct startDate) as DayCountfrom Coption (MAXRECURSION 0)

结果：

DayCount-----------11

或者，您可以使用数字表。在这里我使用master..spt_values：

declare @MinStartDate dateselect @MinStartDate = min(startDate)from @Tselect count(distinct N.number)from @T as T  inner join master..spt_values as N    on dateadd(day, N.Number, @MinStartDate) between T.startDate and dateadd(day, -1, T.endDate)where N.type = 'P'