【技巧】SQL Server分页

SQL Server分页查询是我们经常会用到的功能，下面就为你介绍SQL Server分页查询的相关语句，希望对您学习SQL Server分页查询方面能有所帮助。

1. SQL Server2000

SELECT TOP @pagesize *   

FROM TABLE_NAME   

WHERE id not in   

(  

SELECT TOP @pagesize*(@page-1) id   

FROM TABLE_NAME   

ORDER BY id  

)   

ORDER BY id  

 

2. SQL Server 2005

SQL Server分页查询方法一：

SELECT ...   

FROM   

(  

 SELECT ROW_NUMBER() OVER (ORDER BY ID asc) AS RowNum, ......  

 FROM TABLE_NAME   

) AS T  

WHERE T.RowNum> 10 and T.RowNum<= 20 

 

SQL Server分页查询方法二：

WITH DataList AS   

(   

  SELECT ROW_NUMBER() OVER (ORDER BY O.ID DESC)AS RowNum, ......  

  FROM .....  

  WHERE ......  

)  

SELECT ......  

FROM DataList  

WHERE RowNum BETWEEN 10 AND 20