假设一个分类表Category,定义变量:@PageSize 和 @PageIndex
int @PageSize -- 页面大小
int @PageIndex -- 页面索引
WITH Category AS
(
SELECT ROW_NUMBER() OVER(ORDER BY CategoryID DESC) AS RowNumber,*
FROM [Category]
)
SELECT * FROM Category WHERE RowNumber
BETWEEN @PageSize*(@PageIndex-1) + 1 AND @PageSize * @PageIndex;