WEB开发网
开发学院数据库MSSQL Server 一个根据列的范围分组汇总的Sql存储过程 阅读

一个根据列的范围分组汇总的Sql存储过程

 2009-09-14 00:00:00 来源:WEB开发网   
核心提示:1.需求说明有如下表数据:ID NUM--- ---1 22 33 24 25 126 27 18 59 110 111 1 输入分组参数,比如输入 "2,5,8,10" ,实现按 ID<=2,2<ID<=5,5<ID<=8,8<ID<=10,ID>1

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中,服务器管理器中连接上数据库,找到存储过程右键单步调试。

Tags:一个 根据 范围

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接