数据分页显示

--存储过程分页

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--总记录数
as
declare @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<>'')
begin
set @Fwhere=' where '+@where
set @Lwhere=' and  '+@where
end
else
begin
set @Fwhere=' '
set @Lwhere=' '
end
if(@orderList<>' ')
begin
set @sorderList=' orderby '+@orderList+' '+@orderdy
end
else
begin
set @sorderList=' '
end
set @str=@str+@Fwhere+@sorderList+')'+@Lwhere+@sorderList
exec(@str)
set @sstr='select @rn=count(*) from view_job '+@Fwhere
execute sp_executesql @sstr,N'@rn float out',@rn out
set @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 id
select * from prescription where rownum <=3 and id not in(
       select id from prescription where rownum <=(2-1)*3
)

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