SQL：将事件拆分为多行

Question

请查看我的SQLFiddle，链接如下 (link)

在SQL Server 2008中，我有一个事件开始和结束时间的表，如下图所示。

我需要编写一个查询，以便能够确定每个事件落入哪个工作班次。 我们的班次为12小时，从06:00-18:00和18:00-06:00。

该查询应生成像下图所示的结果。

通过这个查询，我可以计算特定工作班次的总事件持续时间。

我该怎么做，才能从第一张图片得到第二张图片呢？

- Tommy O'Dell

Answer 1

With Shifts As
  (
    Select 1 As Num
      , Cast('2012-05-01 6:00 AM' As datetime) As ShiftStart
      , DateAdd(hh,12,Cast('2012-05-01 6:00 AM' As datetime)) As ShiftEnd
    Union All
    Select Num + 1, ShiftEnd, DateAdd(hh,12,ShiftEnd)
    From Shifts
    Where ShiftEnd < '2012-05-30'
    )
  , Segments As
    (
    Select event_id
      , Case 
        When Shifts.ShiftStart > event_start Then Shifts.ShiftStart 
        Else event_start
        End As start_split_segment        
      , Case 
        When Shifts.ShiftEnd < event_end Then Shifts.ShiftEnd
        Else event_end
        End As end_split_segment          
      , Count(*) Over ( Partition By E.event_id ) As SegmentCount
    From events As E
      Join Shifts
        On E.event_start <= ShiftEnd
          And E.event_end > ShiftStart
      )
Select E.event_id, E.description, E.event_start, E.event_end
  , S.start_split_segment, S.end_split_segment
  , Case When SegmentCount > 1 Then 1 Else 0 End As is_split
  , NullIf(SegmentCount,1) As split_segments
From Segments As S
  Join events As E
    On E.event_id = S.event_id

在这个解决方案中，我生成了每个班次的开始和结束日期的日历。您可以通过将Where ShiftEnd < '2012-05-30'更改为更大的日期来扩展日历。请注意，如果最终有超过50个班次左右，则需要在查询末尾添加Option(Maxrecursion 0);以解除SQL Server的限制。

SQL Fiddle版本

- Thomas

---

干得好！我简直不敢相信你能把它概括得如此简洁。这将为我节省很多麻烦。非常感谢。 - Tommy O'Dell