您可以使用递归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'
