本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。
原帖地址:
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<='[email protected],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>'[email protected],@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
--*/
