使用between分页的,要用rownumber,因为要防止有些id不存在
pagesize=10
pageindex start=(pageindex-1)*10+1 end=pageindex*pagesize
select * from(select row_number() over(order by id) as num,* from tblname) as tbl
where num between start and end
使用top分页
pagesize=10
1页 select top 10 * from tblname where id not in(select top 0 id from tblname)
2页 select top 10 * from tblname where id not in(select top 10 id from tblname)
3页 select top 10 * from tblname where id not in(select top 20 id from tblname)
n页 select top pagesize * from tblname where id not in(select top (n-1)*pagesize id from tblname)
pagesize=10
pageindex | start | end |
---|---|---|
1页 | 1 | 10 |
2页 | 11 | 20 |
3页 | 21 | 30 |
pageindex start=(pageindex-1)*10+1 end=pageindex*pagesize
select * from(select row_number() over(order by id) as num,* from tblname) as tbl
where num between start and end
使用top分页
pagesize=10
1页 select top 10 * from tblname where id not in(select top 0 id from tblname)
2页 select top 10 * from tblname where id not in(select top 10 id from tblname)
3页 select top 10 * from tblname where id not in(select top 20 id from tblname)
n页 select top pagesize * from tblname where id not in(select top (n-1)*pagesize id from tblname)