Gordon Linoff有基于CTE的答案
我已经对所有可用算法进行了性能分析。空白值表示此过程花费了太长时间。这是在单个Core
i7 X920 @ 2GHz芯片上测试的，并带有几个SSD。创建的唯一索引是UserID
AvailStart上的群集。如果您认为可以改善任何性能，请告诉我。
此CTE版本比线性版本差，SQL Server无法以有效的方式执行RN = RN +
1联接。我在下面的一种混合方法中对此进行了纠正，在该方法中，我将第一个CTE保存并索引到表变量中。这仍然需要比基于游标的方法多十倍的IO。
With OrderedRanges as ( Select Row_Number() Over (Partition By UserID Order By AvailStart) AS RN, AvailStart, AvailEnd From dbo.Available Where UserID = 456),AccumulateMinutes (RN, Accum, CurStart, CurEnd) as ( Select RN, 0, AvailStart, AvailEnd From OrderedRanges Where RN = 1 Union All Select o.RN, a.Accum + Case When o.AvailStart <= a.CurEnd Then 0 Else DateDiff(Minute, a.CurStart, a.CurEnd) End, Case When o.AvailStart <= a.CurEnd Then a.CurStart Else o.AvailStart End, Case When o.AvailStart <= a.CurEnd Then Case When a.CurEnd > o.AvailEnd Then a.CurEnd Else o.AvailEnd End Else o.AvailEnd End From AccumulateMinutes a Inner Join OrderedRanges o On a.RN = o.RN - 1)Select Max(Accum + datediff(Minute, CurStart, CurEnd)) From AccumulateMinutes
http://sqlfiddle.com/#!6/ac021/2
经过性能分析后，这里是混合CTE /表变量版本，其性能比除基于游标的方法外要好
Create Function dbo.AvailMinutesHybrid(@UserID int) Returns Int AsBeginDeclare @UserRanges Table ( RN int not null primary key, AvailStart datetime, AvailEnd datetime)Declare @Ret int = Null;With OrderedRanges as ( Select Row_Number() Over (Partition By UserID Order By AvailStart) AS RN, AvailStart, AvailEnd From dbo.Available Where UserID = @UserID)Insert Into @UserRanges Select * From OrderedRanges;With AccumulateMinutes (RN,Accum, CurStart, CurEnd) as ( Select RN, 0, AvailStart, AvailEnd From @UserRanges Where RN = 1 Union All Select o.RN, a.Accum + Case When o.AvailStart <= a.CurEnd Then 0 Else DateDiff(Minute, a.CurStart, a.CurEnd) End, Case When o.AvailStart <= a.CurEnd Then a.CurStart Else o.AvailStart End, Case When o.AvailStart <= a.CurEnd Then Case When a.CurEnd > o.AvailEnd Then a.CurEnd Else o.AvailEnd End Else o.AvailEnd End From AccumulateMinutes a Inner Join @UserRanges o On a.RN + 1 = o.RN)Select @Ret = Max(Accum + datediff(Minute, CurStart, CurEnd)) From AccumulateMinutes Option (MaxRecursion 0)Return @RetEnd
http://sqlfiddle.com/#!6/bfd94
