数据分页显示

--存储过程分页

create proc proc_PagerJob@pageNum varchar(3),--当前页码@pageSize varchar(3),--每页显示行数@pageKey varchar(20),--主键@where varchar(500),--where条件@orderList varchar(50),--排序列名@orderdy varchar(4),--排序方法@pageCount varchar(6) output,--总页数@pageNums varchar(6) output--总记录数asdeclare @str varchar(4000)--sql语句declare @sstr nvarchar(4000)--记录总条数sql语句declare @Fwhere varchar(500)--前置where条件declare @Lwhere varchar(500)--后置where条件declare @SorderList varchar(50)--排序列declare @rn float--系统函数返回总行数if(@where<>'')beginset @Fwhere=' where '+@whereset @Lwhere=' and  '+@where endelsebeginset @Fwhere=' 'set @Lwhere=' 'endif(@orderList<>' ')beginset @sorderList=' orderby '+@orderList+' '+@orderdyendelsebeginset @sorderList=' 'endset @str=@str+@Fwhere+@sorderList+')'+@Lwhere+@sorderListexec(@str)set @sstr='select @rn=count(*) from view_job '+@Fwhereexecute sp_executesql @sstr,N'@rn float out',@rn outset @pageNums = convert(varchar,@rn)set @pageCount=convert(varchar,ceiling(@rn/convert(float,@pageSize)))

 

 

 

--oracle分页--第一步select m.*, ROWNUM rn from ((select * from prescription) m)

select * from (       select m.*, ROWNUM rn from ((select * from prescription order by id) m)) where rn between 1 and 5

--oracle分页2 "通用写法" 在sqlserver中用top

--pageNum 或 pageNo 当前第几页 ，pageSize每页显示的函数,select * from prescription where rownum <=pageSize and id not in(       select id from prescription where rownum <=(pageNum-1)*pageSize)

--sqlserver写法 select top pageSize  * from tb_word where id not in( select top ((pageNum-1)*pageSize) id from tb_word order by id ) order by id

select top 2  * from tb_word where id not in( select top ((2-1)*2) id from tb_word order by id ) order by id

 

--id如果不是主键，需要加order by idselect * from prescription where rownum <=3 and id not in(       select id from prescription where rownum <=(2-1)*3)

--oracle分页3 三个select语句嵌套式实现分页显示 ，效率比第一种好

 

转载于:https://www.cnblogs.com/syscceyz/archive/2011/12/09/2281977.html