--> Title : MSSQL求連續ID內數量合計-常見解決方案
--> Author : wufeng4552
--> Date : 2009-12-04
if object_id('tb') is not null drop table tb
go
create table tb(ID varchar(10),Num decimal(10,2))
insert tb
select '001',200.00 union all
select '002',200.00 union all
select '003',300.00 union all
select '007',500.00 union all
select '008',800.00 union all
select '009',200.00 union all
select '012',100.00 union all
select '013',100.00 union all
select '014',200.00 union all
select '017',100.00 union all
select '018',400.00 union all
select '019',300.00
-->方法 1 臨時表
if object_id('tempdb..#t1')is not null
drop table #t1
if object_id('tempdb..#t2')is not null
drop table #t2
go
--取出起號
select cnt=identity(int,1,1),* into #t1 from tb t where not exists(select 1 from tb where id=t.id-1)order by t.id
--取出止號
select cnt=identity(int,1,1),* into #t2 from tb t where not exists(select 1 from tb where id=t.id+1)order by t.id
select n.[start]+'-'+n.[end]起止號,sum(num)合計
from tb m,
(select a.ID [start],b.ID [end] from #t1 a,#t2 b where a.cnt=b.cnt) n
where m.ID between n.[start] and n.[end]
group by n.[start]+'-'+n.[end]
/*
起止號 合計
--------------------- ---------------------------------------
001-003 700.00
007-009 1500.00
012-014 400.00
017-019 800.00
(4 個資料列受到影響)
*/
--方法 2
select case when min(t.id)!=max(t.id) then min(t.id)+'-'+max(t.id)else min(t.id)end 起止號,
sum(num)合計
from(
select ID,
cnt=cast(ID as int)-(select count(*)from tb n where m.ID>n.ID),
num
from tb m
)t group by cnt
/*
起止號 合計
--------------------- ---------------------------------------
001-003 700.00
007-009 1500.00
012-014 400.00
017-019 800.00
(4 個資料列受到影響)
*/
--方法3
select case when min(t.id)!=max(t.id) then min(t.id)+'-'+max(t.id)else min(t.id)end 起止號,
sum(num)合計
from(
select id,cnt=id-row_number()over(order by getdate()),num from tb
)t group by cnt
/*
起止號 合計
--------------------- ---------------------------------------
001-003 700.00
007-009 1500.00
012-014 400.00
017-019 800.00
(4 個資料列受到影響)
*/
