动态分组查询

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

原帖地址:

http://community.csdn.net/Expert/topic/3428/3428792.xml?temp=.6476251

--示例数据

create table 表(ID int,NUM int)

insert 表 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

go

/*--问题说明:

输入分组参数,比如输入 "3,6" ,实现按 ID<=3,3<ID<=6,ID>6 分组查询

输入分组参数,比如输入 "2,5,8" ,实现按 ID<=2,2<ID<=5,5<ID<=8,ID>8 分组查询

--*/

--查询的存储过程

create proc p_qry

@numlist varchar(1000)

as

set nocount on

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

declare @i int,@pnum varchar(10)

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

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

,@numlist=stuff(@numlist,1,@i,'')

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

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

while @i>0

begin

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

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

,@numlist=stuff(@numlist,1,@i,'')

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

end

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

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

from 表 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.组

order by min(b.id)

go

--调用存储过程进行查询

exec p_qry '2,5,8'

go

--删除测试

drop table 表

drop proc p_qry

/*--测试结果

组 num

---------- -----------

id<=2 5

2<id<=5 16

id>8 1

--*/

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航