mssql 数据库下分页方法总结


数据源准备:
create table #testTb(keyId int)
declare @i int
set @i=1
while @i<100000 begin insert into #testTb(keyId)values(@i) set @i=@i+1 end



一、常用的分页方法简介

1.1 top 方式
set statistics io on
select top 10 * from #testTb
where keyId not in(select top 600 keyId from #testTb)

1.2 max方式
set statistics io on
select top 10 * from #testTb
where keyId>(select max(keyId)
from (select top 600 keyId from #testTb order by keyId)tt)

1.3 row_number方式
set statistics io on
select keyId
from (
select row_number()over(order by keyId) as paiXiLie,*
from (select top 610 * from #testTb)t
)tt
where paiXiLie > 600


从效率上分析,row_number > max > top 方式
从逻辑读上面  row_number > max > top