sql server2005分页rownumber()

获取tb_bottleInfo 的第一页的5条记录

select * from (select *,ROW_NUMBER()  OVER(Order by ID ASC ) AS RowNumber from tb_bottleInfo )
 as b where RowNumber BETWEEN 1 and 5 

与其他表联合查询分页

select * from (select *,ROW_NUMBER()  OVER(Order by ID ASC ) AS RowNumber from tb_bottleInfo )
 as b left join tb_bottleType on b.typeID = tb_bottleType.ID
 where RowNumber BETWEEN 1 and 5 

存储过程

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, CompanyName, 
       ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
       FROM Customers ) 

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

END