SQLServer2005中实现分页查询数据

发布时间：2020-12-12 15:54:41 所属栏目：MsSql教程来源：网络整理

导读：WITH,ROW_NUMBER and OVER这三个关键字是SQL Server2005中新增加的，通过这些关键字来实现分页查询非常简单，下面仅实现一个简单的例子： With Cust AS ( SELECT CustomerID,CompanyName, ROW_NUMBER() OVER ( order by CompanyName) as RowNumber FROM Cust

WITH,ROW_NUMBER and OVER这三个关键字是SQL Server2005中新增加的，通过这些关键字来实现分页查询非常简单，下面仅实现一个简单的例子：

With Cust AS
    ( SELECT CustomerID,CompanyName,
    ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

将上述的代码封装到的存储过程中：

CREATE PROC GetCustomersByPage

@PageSize int,@PageNumber int 

AS 

Declare @RowStart int 
Declare @RowEnd int 

if @PageNumber > 0 
Begin 

SET @PageNumber = @PageNumber -1 

SET @RowStart = @PageSize * @PageNumber + 1; 
SET @RowEnd = @RowStart + @PageSize - 1 ; 

With Cust AS 
     ( SELECT CustomerID,
       ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
FROM Customers ) 

select * 
from Cust 
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end

END

更详细的内容参考http://www.codeproject.com/KB/database/PagingResults.aspx

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!