合并 SQL 中的相邻行? [英] Merge adjacent rows in SQL?

问题描述

我正在根据员工工作的时间段做一些报告.在某些情况下，数据包含两个单独的记录，实际上是一个时间块.

I'm doing some reporting based on the blocks of time employees work. In some cases, the data contains two separate records for what really is a single block of time.

这是表格的基本版本和一些示例记录:

Here's a basic version of the table and some sample records:

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

在示例中，最后两条记录在时间上是连续的.我想编写一个查询来组合任何相邻的记录，因此结果集是这样的:

In the example, the last two records are contiguous in time. I'd like to write a query that combines any adjacent records so the result set is this:

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

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

Ideally, it should also be able to handle more than 2 adjacent records, but that is not required.

推荐答案

这篇文章为你的问题提供了很多可能的解决方案

This article provides quite a few possible solutions to your question

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;

这篇关于合并 SQL 中的相邻行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！