sqlserver 分页存储过程-优快云博客

本文详细介绍了如何使用SQL进行分页查询，并通过存储过程优化查询效率。包括分页逻辑实现、存储过程创建及测试，适用于数据库管理与查询优化。

--分页
--求共多少页pageCount   当前页的数据
--已知 1 每页显示几条   pageSize    2 当前页码 pageIndex

select 6/3
select CEILING( 7.0/3)

--第一页    pageSize=3
select * from
(select *,ROW_NUMBER() over(order by ptime desc) as num from photos) as t
where num between 1 and 3 order by ptime desc
--第二页
select * from
(select *,ROW_NUMBER() over(order by ptime desc) as num from photos) as t
where num between 4 and 6 order by ptime desc

--第三页
select * from
(select *,ROW_NUMBER() over(order by ptime desc) as num from photos) as t
where num between 7 and 9 order by ptime desc

--第pageIndex页 pageSize=3
select * from
(select *,ROW_NUMBER() over(order by ptime desc) as num from photos) as t
where num between (pageIndex-1)*pageSize+1 and pageIndex*pageSize order by ptime desc

--分页的存储过程
create proc usp_photos
   @pageIndex int,       --当前页码
   @pageSize int,       --页容量
   @pageCount int output --共多少页输出参数
as
   declare @count int
   select @count=COUNT(*) from Photos
   set @pageCount = CEILING( @count*1.0/@pageSize)

   select * from
(select *,ROW_NUMBER() over(order by ptime desc) as num from photos) as t
where num between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize order by ptime desc

--测试存储过程
declare @n int
exec usp_photos 2,2,@n output
print @n

use myphotos
select * from PhotoType