合并连续号段
张数 |起号 | 止号
474 |231527 |232000
550 |240451 |241000
642 |241359 |242000
526 |244475 |245000
557 |246444 |247000
100 |247901 |248000
2000 |248001 |250000
20000|250001 |270000
50000|270001 |320000
----------------------------
求合并脚本
[解决办法]
Select *
Into #tb
From (
Select 474 as 张数,231527 as 起号,232000 as 止号 Union All
Select 550 ,240451 ,241000 Union All
Select 642 ,241359 ,242000 Union All
Select 526 ,244475 ,245000 Union All
Select 557 ,246444 ,247000 Union All
Select 100 ,247901 ,248000 Union All
Select 2000 ,248001 ,250000 Union All
Select 20000,250001 ,270000 Union All
Select 50000,270001 ,320000)as tt
--查询
with t as (Select COALESCE(t2.张数,0) + t1.张数 as 张数,t1.起号,COALESCE(t2.止号,t1.止号) as 止号
From #tb as t1
Left Join #tb as t2
ON t1.止号 = t2.起号 - 1
Left Join #tb as t3
On t2.止号 = t3.起号 - 1
)
Select *
From t as t1
Where Not Exists(
Select *
From t as t2
where t1.止号 = t2.止号
And t1.张数 > t2.张数
)
---结果
474231527232000
550240451241000
642241359242000
526244475245000
557246444247000
2100247901250000
22000248001270000
50000270001320000
select 100 张数, 247901 起号,248000 止号
into #t
union all select 2000 , 248001 ,250000
union all select 20000, 250001 ,270000
union all select 50000, 270001 ,320000
;With t as
(
select 张数,起号,止号
from #t
union all
select a.张数+b.张数,a.起号,b.止号
from t a join #t b
on a.止号=b.起号-1
)
select max(c.张数) 张数,Min(c.起号) 起号, c.止号
from t c
left join t d
on c.止号=d.起号-1
where d.张数 is null
group by c.止号
