sqlserver 分页存储过程
--分页
--求 共多少页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