特殊分页处理

原帖地址:
http://community.youkuaiyun.com/Expert/topic/3662/3662135.xml?temp=.4289972

--测试数据
create table tb(ID int primary key,grade varchar(10),uptime datetime)
insert tb select 1 ,'a','2004-12-11'
union all select 2 ,'b','2004-12-11'
union all select 3 ,'c','2004-12-11'
union all select 4 ,'a','2004-12-12'
union all select 5 ,'c','2004-12-13'
union all select 6 ,'c','2004-12-13'
union all select 7 ,'a','2004-12-14'
union all select 8 ,'a','2004-12-15'
union all select 9 ,'b','2004-12-16'
union all select 10,'b','2004-12-17'
union all select 11,'a','2004-12-17'
go

/*--分页处理要求

每页5条记录: c类2条 b类1条 a类2条
数据顺序,uptime desc,grade=c>b>a,id desc
某类不足时,由它的后续类补齐
--*/

--分页处理的存储过程
create proc p_split
@currentpage int=1, --要显示的当前页
@pagesize int=5 --每页的大小(如果调整了这个,则存储过程中,排序的处理也要做相应的修改,即:case grade when 'c' then 2 when 'b' then 1 when 'a' then 2 end 部分,这个控制每类/每页多少条记录as
set nocount on
set @currentpage=@currentpage*@pagesize
set rowcount @currentpage
select * into #t from tb a
order by ((select count(*) from tb where grade=a.grade and(uptime>a.uptime or uptime=a.uptime and id>=a.id))-1)
/case grade when 'c' then 2 when 'b' then 1 when 'a' then 2 end
,case grade when 'c' then 1 when 'b' then 2 when 'a' then 3 end,id desc
if @currentpage>@pagesize
begin
set @currentpage=@currentpage-@pagesize
set rowcount @currentpage
delete from #t
end
select * from #t
order by case grade when 'c' then 1 when 'b' then 2 when 'a' then 3 end
,uptime desc,id desc
go

--调用
exec p_split 1
exec p_split 2
exec p_split 3
go

--删除测试
drop table tb
drop proc p_split

/*--测试结果

ID grade uptime
----------- ---------- -------------------------
6 c 2004-12-13 00:00:00.000
5 c 2004-12-13 00:00:00.000
10 b 2004-12-17 00:00:00.000
11 a 2004-12-17 00:00:00.000
8 a 2004-12-15 00:00:00.000

ID grade uptime
----------- ---------- -------------------------
3 c 2004-12-11 00:00:00.000
9 b 2004-12-16 00:00:00.000
2 b 2004-12-11 00:00:00.000
7 a 2004-12-14 00:00:00.000
4 a 2004-12-12 00:00:00.000

ID grade uptime
----------- ---------- -------------------------
1 a 2004-12-11 00:00:00.000
--*/