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]


/*


起止號                  合計


--------------------- www.52mvc.com---------------------------------------


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


 


*/