经典分页推荐

/***************************************************************************************
===================================SQL语句分页(1)========================
***************************************************************************************/
-----------------------------------------------------------------------------------------------
declare @pagesize int,--每页大小
declare @page int,--当前页
--ID(主键，自动增长)--
select top @pagesize * from User
where ID>all(select top (@page-1)*@pagesize ID from User order by ID)
order by ID


---正排
select top 4 * from MS_LimitHistory
where limitid>all(select top 4 limitid from MS_LimitHistory order by limitid)
order by limitid
--倒排
select top 4 * from MS_LimitHistory
where limitid<all(select top 0 limitid from MS_LimitHistory order by limitid desc)
order by limitid desc
-----------------------------------------------------------------------------------------------
/****************************************************************************************
===================================SQL语句分页(2)强烈推荐================
****************************************************************************************/
-----------------------------------------------------------------------------------------------
declare @pagesize int,--每页大小
declare @page int,--当前页

SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY id ASC) RowNum, * FROM FM_PD_NETVALUE ) OrderData
WHERE RowNum BETWEEN @pagesize*(@page-1)+1 and @pagesize*@page
例子：
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY id ASC) RowNum, * FROM FM_PD_NETVALUE ) OrderData
WHERE RowNum between 10 *(1-1)+1 and 10*1

-----------------------------------------------------------------------------------------------