MSSQL求連續ID內數量合計-常見解決方案

--> 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 個資料列受到影響)

 

 

 

*/