动态分组查询

 1<id<=6,id>6 分组查询   
 2输入分组参数,比如输入 "2,5,8" ,实现按 ID&lt;=2,2<id<=5,5<id<=8,id>8 分组查询   
 3\--*/ 
 4
 5\--查询的存储过程   
 6create proc p_qry   
 7@numlist varchar(1000)   
 8as   
 9set nocount on   
10declare @t table(id int identity,组 varchar(10),a int,b int)   
11declare @i int,@pnum varchar(10)   
12select @i=charindex(',',@numlist+',')   
13,@pnum=left(@numlist,@i-1)   
14,@numlist=stuff(@numlist,1,@i,'')   
15,@i=charindex(',',@numlist)   
16insert @t select 'id&lt; ='+@pnum,null,@pnum   
17while @i&gt;0   
18begin   
19insert @t select @pnum+'<id<='+left(@numlist,@i-1),@pnum,left(@numlist,@i-1) 'id="" ,@i="charindex(',',@numlist)" ,@numlist="stuff(@numlist,1,@i,'')" @pnum="left(@numlist,@i-1)" @t="" end="" insert="" select="">'+@numlist,@numlist,null 
20
21select b.组,num=sum(a.num)   
22from 表 a,@t b   
23where case   
24when b.a is null then case when a.id&lt;=b.b then 1 else 0 end   
25when b.b is null then case when a.id&gt;b.a then 1 else 0 end   
26else case when a.id&gt;b.a and a.id&lt;=b.b then 1 else 0 end   
27end=1   
28group by b.组   
29order by min(b.id)   
30go 
31
32\--调用存储过程进行查询   
33exec p_qry '2,5,8'   
34go 
35
36\--删除测试   
37drop table 表   
38drop proc p_qry 
39
40/*--测试结果 
41
42组 num   
43\---------- -----------   
44id&lt;=2 5   
452<id<=5 16="" id="">8 1   
46\--*/</id<=5></id<='+left(@numlist,@i-1),@pnum,left(@numlist,@i-1)></id<=5,5<id<=8,id></id<=6,id>