sql - 组关闭号

扫码查看

我有一个带有2列整数的表。第一列代表开始索引,第二列代表结束索引。

START END
1     8
9     13
14    20
20    25
30    42
42    49
60    67

到目前为止很简单。我想做的是将随后的所有记录归为一组:
START END
1     25
30    49
60    67

一条记录可以跟在与上一个结束索引相同的索引上,也可以由1的边距开始:
START END
1     10
10    20


START END
1     10
11    20

都会导致
START END
1     20

我正在使用SQL Server 2008 R2。

任何帮助都会很棒

最佳答案

这适用于您的示例,请让我知道它是否不适用于其他数据

create table #Range
(
  [Start] INT,
  [End] INT
)

insert into #Range ([Start], [End]) Values (1, 8)
insert into #Range ([Start], [End]) Values (9, 13)
insert into #Range ([Start], [End]) Values (14, 20)
insert into #Range ([Start], [End]) Values (20, 25)
insert into #Range ([Start], [End]) Values (30, 42)
insert into #Range ([Start], [End]) Values (42, 49)
insert into #Range ([Start], [End]) Values (60, 67)



;with RangeTable as
(select
    t1.[Start],
    t1.[End],
    row_number() over (order by t1.[Start]) as [Index]
from
    #Range t1
where t1.Start not in (select
                      [End]
               from
                  #Range
                  Union
               select
                  [End] + 1
               from
                  #Range
               )
)
select
    t1.[Start],
    case
   when t2.[Start] is null then
        (select max([End])
                     from #Range)
       else
        (select max([End])
                     from #Range
                     where t2.[Start] > [End])
end as [End]
from
    RangeTable t1
left join
    RangeTable t2
on
    t1.[Index] = t2.[Index]-1

drop table #Range;

10-05 20:49
查看更多