一個根據列的范圍分組匯總的Sql存儲過程

1.需求說明

有如下表數據：

ID NUM
----------- -----------
1 2
2 3
3 2
4 2
5 12
6 2
7 1
8 5
9 1
10 1
11 1

輸入分組參數,比如輸入 "2,5,8,10" ,實現按 ID<=2,2<ID<=5,5<ID<=8,8<ID<=10,ID>10 分組查詢，要得到下面的數據：

groupdata num
---------- -----------
id<=2 5
2<id<=5 16
5<id<=8 8
8<id<=10 2
id>10 1

2.存儲過程如下：

--測試數據

create table TestData(ID int,NUM int)

insert TestData select 1,2

union all select 2,3

union all select 3,2

union all select 4,2

union all select 5,12

union all select 6,2

union all select 7,1

union all select 8,5

union all select 9,1

union all select 10,1

union all select 11,1

go



create proc spgroupcol

@numlist varchar(1000)

as

set nocount on

declare @t table(id int identity,groupdata varchar(10),a int,b int)

declare @i int,@pnum varchar(10),@j int

select @i=charindex(',',@numlist)

,@pnum=left(@numlist,@i-1)

insert @t select 'id<='+@pnum,null,@pnum



while @i>=1

begin

select @numlist=substring(@numlist,@i+1,len(@numlist)-@i)

select @j=charindex(',',@numlist) ;

if @i=@j

begin

insert @t select @pnum+'<id<='+substring(@numlist,0,@i),@pnum,substring(@numlist,0,@i)

select @pnum=left(@numlist,@i-1);

end

else

begin

insert @t select @pnum+'<id<='+substring(@numlist,0,@i+1),@pnum,substring(@numlist,0,@i+1)

select @pnum=left(@numlist,@i);

end

select @i=charindex(',',@numlist) ;

end



insert @t select 'id>'+@numlist,@numlist,null

select b.groupdata,num=sum(a.num)

from TestData a,@t b

where case

when b.a is null then case when a.id<=b.b then 1 else 0 end

when b.b is null then case when a.id>b.a then 1 else 0 end

else case when a.id>b.a and a.id<=b.b then 1 else 0 end

end=1

group by b.groupdata

order by min(b.id)

go



spgroupcol '2,5,8,10'

drop table TestData

sql存儲過程的單步調試要在Vs2008中，服務器管理器中連接上數據庫，找到存儲過程右鍵單步調試。

轉自：http://www.cnblogs.com/carysun/