SQL server分页方法

说一下SQL server的分页与MySQL的分页的不同，mysql的分页直接是用limit (pageIndex-1),pageSize就可以完成，但是SQL server 并没有limit关键字，所以分页起来比较麻烦。

SQL server分页大概有四种：三重循环；利用max（主键）;利用row_number关键字，offset/fetch next关键字（主要讲解后面两种）。

 

利用row_number关键字

SELECT * from
(SELECT *,ROW_NUMBER() OVER (ORDER BY CreateDate DESC) AS RowNumber FROM BX_Warranty ) T1 WHERE RowNumber BETWEEN 10 AND 20

结果

 

SELECT ContractName ,ContractNO, State,CreateDate FROM
(SELECT ContractName ,ContractNO,CreateDate, State,ROW_NUMBER() OVER (ORDER BY CreateDate DESC) AS RowNumber FROM dbo.MALL_Contract) T1
WHERE RowNumber BETWEEN 10 AND 20

 

FETCH NEXT（SQL server 2012版本及以上才有）

 

SELECT * FROM BX_Warranty WHERE CliqueID='37CF8C26-F65C-4D60-B442-144B5637FCEF' ORDER BY CreateDate OFFSET (1-1)*10 ROWS FETCH NEXT 10 ROWS ONLY

结果