以下操作是基于Sql Server 2000上的实例数据库Northwind。为数据表Products实现分页:
1、在Sql Server 2000下没有提供现成的方法可供使用,只能自己想办法,我的实现方式如下:
declare @pageSize int
declare @pageIndex int
set @pageSize = 10
set @pageIndex = 1
Select Count(b.ProductID) as row_id,a.ProductID
from Products a
inner join Products b on a.ProductID >= b.ProductID
group by a.ProductID
having Count(b.ProductID) between @pageSize * (@pageIndex - 1) and @pageSize * @pageIndex
order by row_id
2、Sql Server 2005下可以借助函数ROW_NUMBER() 实现分页
declare @pageSize int
declare @pageIndex int
set @pageSize = 10
set @pageIndex = 1
With ProductTemp
(
Select ProductID,ROW_NUMBER() OVER(ORDER BY ProwductID ) as row_id
From Products
)
Select * From ProductTemp where row_id between @pageSize * (@pageIndex - 1) and @pageSize * @pageIndex
3、在Oracle中是最为简单的,直接应用rownum函数。
declare @pageSize int
declare @pageIndex int
set @pageSize = 10
set @pageIndex = 1
Select rownum,ProductID From Products where rownum between @pageSize * (@pageIndex - 1) and @pageSize * @pageIndex