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 料列受到影)
*/