收藏的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 個資料列受到影響)          
          
  
          
*/