SQL Server 分页解决方案

分页方案一：(利用Not In和SELECT TOP分页)

 例子：

每页显示10条记录，显示第一页记录：

 

SELECT TOP 10 * FROM Production.Product WHERE
(ProductID not in (SELECT TOP 0 ProductID FROM Production.Product ORDER BY ProductID))
ORDER BY ProductID

结果如下：

 

 

 

每页显示10条记录，显示第二页记录：

SELECT TOP 10 * FROM Production.Product WHERE 
(ProductID not in (SELECT TOP 10 ProductID FROM Production.Product ORDER BY ProductID))
ORDER BY ProductID

结果如下：

 

 可以总结为：

SELECT TOP 页大小 * FROM Table WHERE 
(ID NOT IN (SELECT TOP 页大小*(页数-1) ID FROM Table ORDER BY ID))
ORDER BY ID

 

分页方案二：(利用SQL Server 2005新增的 ROW_NUMBER()分页)

 例子：

每页显示10条记录，显示第一页记录：

WITH Products AS
(SELECT 
ROW_NUMBER() OVER (order by ProductID) as RowNumber,*
FROM  Production.Product )
SELECT *
FROM Products
WHERE RowNumber between 1 and 10; 

结果如下：

 

 

每页显示10条记录，显示第二页记录：

 

WITH Products AS
(SELECT 
ROW_NUMBER() OVER (order by ProductID) as RowNumber,*
FROM  Production.Product )
SELECT *
FROM Products
WHERE RowNumber between 11 and 20; 

结果如下：

 

可以总结为：

 

WITH Products AS
(SELECT 
ROW_NUMBER() OVER (order by ProductID) as RowNumber,*
FROM  Production.Product )
SELECT *
FROM Products
WHERE RowNumber between 页大小*(页数-1)+1 and 页大小*页数;