本文共 2044 字，大约阅读时间需要 6 分钟。
原帖地址:
--测试数据
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 部分,这个控制每类/每页多少条记录asset nocount onset @currentpage=@currentpage*@pagesizeset rowcount @currentpageselect * into #t from tb aorder 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 descif @currentpage>@pagesizebegin set @currentpage=@currentpage-@pagesize set rowcount @currentpage delete from #tendselect * from #torder by case grade when 'c' then 1 when 'b' then 2 when 'a' then 3 end ,uptime desc,id descgo--调用
exec p_split 1exec p_split 2exec p_split 3go--删除测试
drop table tbdrop proc p_split/*--测试结果
ID grade uptime ----------- ---------- -------------------------6 c 2004-12-13 00:00:00.0005 c 2004-12-13 00:00:00.00010 b 2004-12-17 00:00:00.00011 a 2004-12-17 00:00:00.0008 a 2004-12-15 00:00:00.000
ID grade uptime ----------- ---------- -------------------------3 c 2004-12-11 00:00:00.0009 b 2004-12-16 00:00:00.0002 b 2004-12-11 00:00:00.0007 a 2004-12-14 00:00:00.0004 a 2004-12-12 00:00:00.000 ID grade uptime ----------- ---------- -------------------------1 a 2004-12-11 00:00:00.000--*/Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=225446