四种SQL分页语句

来源：互联网发布：义乌涉外数据服务中心编辑：程序博客网时间：2024/05/18 17:57

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()

0 0