连续数字分组的问题？？？？？？？？？

[建军黄]

表A中有数据：
号 数量
1 100
2 100
3 120
4 60
9 2000
10 3000
11 1000

请问如何得到如下结果
号 数量累计
1 ~ 4 380
9 ~ 11 6000

[子陌红尘]

create table #T(号 int,数量 int)
insert into #T select 1 ,100
insert into #T select 2 ,100
insert into #T select 3 ,120
insert into #T select 4 ,60
insert into #T select 9 ,2000
insert into #T select 10,3000
insert into #T select 11,1000


select
号 = rtrim(e.id1)+'~'+rtrim(e.id1),
累计数量 = sum(f.数量)
from
(select
id1 = c.号,id2 = min(d.号)
from
(select
a.号
from
#t a
where
not exists(select 1 from #t where 号=a.号-1)) c ,
(select
a.号
from
#t a
where
not exists(select 1 from #t where 号=a.号+1)) d
where
c.号 < d.号
group by
c.号) e,
#t f
where
f.号 between e.id1 and e.id2
group by
e.id1 ,e.id2

[子陌红尘]

--生成测试数据
--------------------------------
create table #T(号 int,数量 int)
insert into #T select 1 ,100
insert into #T select 2 ,100
insert into #T select 3 ,120
insert into #T select 4 ,60
insert into #T select 9 ,2000
insert into #T select 10,3000
insert into #T select 11,1000



--执行查询
-------------------------------------------------------------------------------------
select
号 = rtrim(e.号1)+'~'+rtrim(e.号2),
累计数量 = sum(f.数量)
from
(select 号1 = c.号,号2 = min(d.号)
from
(select a.号 from #t a where not exists(select 1 from #t where 号=a.号-1)) c,
(select b.号 from #t b where not exists(select 1 from #t where 号=b.号+1)) d
where c.号 < d.号
group by c.号) e,
#t f
where f.号 between e.号1 and e.号2
group by e.号1 ,e.号2


--输出结果
-----------------
号 累计数量
----- --------
1~4 380
9~11 6000

[天地客人]

再建个临时表，在过程中处理，也行

[WZZ]

SELECT a.COL1, SUM(数量) 数量 FROM(
SELECT '1-4' AS COL1, 数量 FROM #T WHERE 号 >=1 AND 号 <=4
UNION ALL
SELECT '5-11' AS COL1, 数量 FROM #T WHERE 号 >4 AND 号 <=11
) AS a
GROUP BY a.COL1

[建军黄]

不好意思，没有及时结贴，多谢 libin_ftsafe(子陌红尘)