在sql server 2005 的新方法ROW_NUMBER做一个查询
select row_number() over (order by ProductID) as Row,Name from Product
可以看到返回结果中每条记录都有一个唯一的表示其序列号的标志。
例如我们在分页中要获取的6到第10条记录就可以采用下面的方法
select Row,Name from
(select ROW_NUMBER() over(order by ProductID) as Row,Name from Product)
as ProductsWithRowNumbers
where Row >= 6 and Row <=10
返回相关的记录。
那我们就利用row_number() 做一个存储过程分页
carate proc [dbo].[page_tbale1]
(
@pageNo int, --当前页数值,最小为1,表示第一页
@pageSize int, --每页显示条数
@PageCount int out,--获取总页数
)
as
set nocount on --使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。--获取总页数
declare @itemscount int
select @PageCount =CEILING( (count(ID)+0.0)/@PageSize)
from Product
order by ProductID
if(@pageNo <> 1)
begin
set @itemscount=(@pageNo-1)*@pageSize+1
select Row,Name from
(select ROW_NUMBER() over(order by ProductID) as Row,Name from Product)
as ProductsWithRowNumbers
where Row >= @itemscount and Row <= @pageNo * @pageSize
end
else
begin
select Row,Name from
(select ROW_NUMBER() over(order by ProductID) as Row,Name from Product)
as ProductsWithRowNumbers
where Row >= 1 and Row <= @pageSize
end
set rowcount 0
