小编典典

合并SQL中的相邻行?

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

理想情况下,它还应该能够处理两个以上的相邻记录,但这不是必需的。


阅读 201

收藏
2021-03-10

共1个答案

小编典典

本文为您的问题提供了许多可能的解决方案

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;
2021-03-10