合并SQL中的相邻行？

我正在做一些基于员工事变时刻的陈诉.在某些环境下,数据包括两个单独的记录,用于确切的单个时刻块.

这是该表的根基版本和一些示例记录：

EmployeeID
StartTime
EndTime

数据：

EmpID      Start         End
----------------------------
#1001   10:00 AM    12:00 PM
#1001    4:00 PM     5:30 PM
#1001    5:30 PM     8:00 PM

在该示例中,最后两个记录在时刻上是持续的.我想写一个组合任何相邻记录的查询,以是功效集是这样的：

EmpID      Start         End
----------------------------
#1001   10:00 AM    12:00 PM
#1001    4:00 PM     8:00 PM

抱负环境下,它还应该可以或许处理赏罚2个以上的相邻记录,但这不是必须的.

办理要领

http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to-packing-date-and-time-intervals-puzzle-136851

这个好像是最直接的：

WITH StartTimes AS
(
  SELECT DISTINCT username,starttime
  FROM dbo.Sessions AS S1
  WHERE NOT EXISTS
    (SELECT * FROM dbo.Sessions AS S2
     WHERE S2.username = S1.username
       AND S2.starttime < S1.starttime
       AND S2.endtime >= S1.starttime)
),EndTimes AS
(
  SELECT DISTINCT username,endtime
  FROM dbo.Sessions AS S1
  WHERE NOT EXISTS
    (SELECT * FROM dbo.Sessions AS S2
     WHERE S2.username = S1.username
       AND S2.endtime > S1.endtime
       AND S2.starttime <= S1.endtime)
)
SELECT username,starttime,(SELECT MIN(endtime) FROM EndTimes AS E
   WHERE E.username = S.username
     AND endtime >= starttime) AS endtime
FROM StartTimes AS S;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!