我有一个带有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;