张数 | 起号 | 止号
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
----------------------------
求合并脚本
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
----------------------------
求合并脚本
解决方案 »
- jdbc连接sqlserver的问题
- 数据库怎么找不到了
- 这2种SQL写法的区别是什么?能说哪种效率高么?
- 求一条SQL统计语句
- 哪有SQL Server 2000 reporting service 企业版 下载
- 两个database的各有一个user表,如何用sp同步用户记录
- 求助一条将日期当前所属周重新编号的SQL语句
- 怎么对数据库进行加密
- 怎样在win98和winme下安装sql2000((带查询分析器与企业管理器)?
- 请问:如何用VB实现将图片导入ACCESS数据库
- 用ibatis框架链接sqlserver库,插入数据时,中文有时候会出现后边多一个问号?
- 动态sql语句中调用存储过程
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.张数
)
---结果
474 231527 232000
550 240451 241000
642 241359 242000
526 244475 245000
557 246444 247000
2100 247901 250000
22000 248001 270000
50000 270001 320000
目测楼主要这个效果。
还是有问题的 你看你最后处理的这个号段
2100 247901 250000 ---A
22000 248001 270000 ---B
50000 270001 320000 ---C
B中包含A一部分
B和C没有连上
这个怎么处理呢
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.张数 --改了此处
)
---结果
474 231527 232000
550 240451 241000
642 241359 242000
526 244475 245000
557 246444 247000
2100 247901 250000
22000 248001 270000
70000 250001 320000
上面有点小失误。
select 474 张数 , 231527 起号,232000 止号
into #t
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;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.止号
2000 , 248001 ,250000
20000, 250001 ,270000
50000, 270001 ,320000这个是可以合并的 这个怎么处理
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.止号result:
张数 起号 止号
72100 247901 320000
楼主为什么不先测试一下我的代码,再提问呢