sql - 将两个日期时间字段组合成 T-SQL 中的两个单独的列

Question

我正在尝试创建一个 View 来显示具有开始和结束时间的事件。此 View 应从仅具有事件时间字段和事件类型字段的现有表中收集。

所以当前的 EventTable 看起来是这样的:

EventTime       | BooleanField
------------------------------
1/1/2010 6:00AM        1
1/2/2010 6:00AM        0
1/3/2010 6:00AM        1
1/4/2010 6:00AM        1
1/5/2010 6:00AM        0

结果集应该是这样的

StartTime       | EndTime
-----------------------------
1/1/2010 6:00AM   1/2/2010 6:00AM
1/3/2010 6:00AM   1/5/2010 6:00AM

因此 View 应该显示 bool 字段为真的时间段。

在 SQL Server 2008 中是否有实现此目的的简单解决方案？

谢谢你的帮助!

Answer 1

您可以尝试类似(完整示例)

DECLARE @EventTable TABLE(
        EventTime DATETIME,
        BooleanField INT
)

INSERT INTO @EventTable (EventTime,BooleanField) SELECT '1/1/2010 6:00AM',1 
INSERT INTO @EventTable (EventTime,BooleanField) SELECT '1/2/2010 6:00AM',0 
INSERT INTO @EventTable (EventTime,BooleanField) SELECT '1/3/2010 6:00AM',1 
INSERT INTO @EventTable (EventTime,BooleanField) SELECT '1/4/2010 6:00AM',1 
INSERT INTO @EventTable (EventTime,BooleanField) SELECT '1/5/2010 6:00AM',0

;WITH Dates AS (
        SELECT  *,
                (SELECT MIN(EventTime) FROM @EventTable WHERE EventTime > e.EventTime AND BooleanField = 0) EndDate
        FROM    @EventTable e
        WHERE   BooleanField = 1
)
SELECT  MIN(EventTime) StartDate,
        EndDate
FROM    Dates
GROUP BY EndDate