SQL Server 分页查询

一、使用 max id

SELECT TOP [pageSize] * FROM [TableName] WHERE id >= (SELECT MAX(ID) FROM select top ((pageIndex-1)*pageSize+1) id
from [TableName]
order by  id asc) maxId) order by id;

二、使用row_number()，sql 2005 版本以上支持

#方式一：
SELECT TOP pageSize * FROM (SELECT ROW_NUMBER() rownums,* FROM [tableName]) temp_row WHERE rownums>((pageIndex-1)*pageSize)
#方式二：
WITH tabela AS(
  SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) rownums ,* FROM [tableName]
)
SELECT * FROM tabela WHERE tabela.rownums BETWEEN 1 AND 2

三、使用offset /fetch next，sql 2012版本以上才有

select * from [tableName]
order by ID DESC 
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;