这是我能够弄清楚的2008年最简单的版本：
; with Data (Date) as (select StartDate from Datesunionselect EndDate from Dates),Ranges (StartDate, Status) as (select D.Date, D2.Statusfrom Data Douter apply ( select top 1 D2.Status from Dates D2 where D2.StartDate <= D.Date and D2.EndDate > D.Date order by case when Status = 'on' then 1 else 2 end) D2)select R.StartDate,(select min(D.Date) from Data D where D.Date > R.StartDate) as EndDate,Statusfrom Ranges Rorder by R.StartDate
即使状态与以前相同，它也会从每个起点/终点开始返回新行。没有找到任何简单的方法来组合它们。
编辑：将第一个CTE更改为此将合并行：
; with Data (Date) as (select distinct StartDate from Dates D1where not exists (Select 1 from Dates D2 where D2.StartDate < D1.StartDate and D2.EndDate > D1.StartDate and Status = 'on')unionselect distinct EndDate from Dates D1where not exists (Select 1 from Dates D2 where D2.StartDate < D1.EndDate and D2.EndDate > D1.EndDate and Status = 'on')),
