四种SQL分页语句

declare @pagesize int =10

declare @pageindex int =3

--------------------rownum------------(强烈推荐)

declare @start int =(@pageindex-1)*@pagesize+1

declare @end int =@pageindex* @pagesize

select *  from (

select *,ROW_NUMBER()over(order by id)as rownum from  [ShareInfo_Test].[dbo].Tb_CompanyColConfig -- 增加排序列

)as A

where a.rownum between @start and @end 

-----------------------TOP-----------

SELECT TOP (@pagesize) *

FROM [ShareInfo_Test].[dbo].Tb_CompanyColConfig

WHERE (ID NOT IN

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

         FROM [ShareInfo_Test].[dbo].Tb_CompanyColConfig

         ORDER BY id))

ORDER BY ID

----------------------max(id)

SELECT TOP (@pagesize) *

FROM [ShareInfo_Test].[dbo].Tb_CompanyColConfig

WHERE (ID >

          (SELECT MAX(id)

         FROM (SELECT TOP ((@pageindex-1)* @pagesize) id

                 FROM [ShareInfo_Test].[dbo].Tb_CompanyColConfig

                 ORDER BY id) AS T))

ORDER BY ID

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

效率最快的是用row_number

其次是max(id)

最后是 id not in()