关闭

动态分组查询

3335人阅读 评论(3) 收藏 举报

原帖地址:
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
--*/

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1280497次
    • 积分:14888
    • 等级:
    • 排名:第828名
    • 原创:178篇
    • 转载:9篇
    • 译文:0篇
    • 评论:881条
    最新评论