SQL：合并日期范围 [英] SQL: Merge Date Ranges

问题描述

我有一张表格，描述了一个商业工作日历的工作片段（日期格式为24小时格式）

I've a table, which describes work slices of a business working calendar: (date format is 24 hours format)

PK  | STARTDATE          | ENDDATE
__________________________________________
1   | 2012/07/21 02:00   | 2012/07/21 04:00
2   | 2012/07/21 03:00   | 2012/07/21 10:00
3   | 2012/07/21 06:00   | 2012/07/21 17:00
4   | 2012/07/21 18:00   | 2012/07/21 19:00

现在，我喜欢合并日期范围开始和结束日期）：

Now, I like to merge the date ranges (within a given start and end date) like this:

PK  | STARTDATE          | ENDDATE
__________________________________________
1   | 2012/07/21 02:00   | 2012/07/21 17:00
2   | 2012/07/21 18:00   | 2012/07/21 19:00

有没有办法使用SQL97标准？如果是这样，其他操作是什么（例如，如果我想要进行一个合并，结果应该是

Is there a way to do this with SQL97 standard? If so, what is with other operations (e.g. if I want to to an invered merge, the result should be

PK  | STARTDATE          | ENDDATE
__________________________________________
1   | 2012/07/21 00:00   | 2012/07/21 02:00
2   | 2012/07/21 19:00   | 2012/07/22 00:00

推荐答案

以下是使用SQL Server语法的示例。首先确定头，或者没有先前重叠的行的行要确定头的最后一个子，它将查找比下一个头小的最后一行，这是SQL：

Here's an example using SQL Server syntax. First it determines the "heads", or rows that have no previous overlapping rows. To determine the last "child" of a "head", it looks for the last row that is smaller than the next "head". Here's the SQL:

; with  heads as
        (
        select  row_number() over (order by head.StartDate) as PK
        ,       *
        from    YourTable head
        where   not exists 
                (
                select  *
                from    YourTable prev
                where   prev.StartDate < head.StartDate
                        and head.StartDate < prev.EndDate
                )
        )
select  row_number() over (order by h.StartDate) as PK
,       h.StartDate
,       max(yt.EndDate) as EndDate
from    heads h
left join
        heads nh
on      nh.PK = h.PK + 1
left join
        YourTable yt
on      h.StartDate <= yt.StartDate
        and (yt.StartDate < nh.StartDate or nh.StartDate is null)
group by
        h.StartDate

SQL Fiddle的实例。

这篇关于SQL：合并日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！