特殊分页处理

原帖地址:
http://community.csdn.net/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
--*/