下面以取第 11 - 15 的数据为例
1. row_number() over()加序号,通过序号取分页
select *
from
(
select row_number() over(order by createTime desc) as id, *
from test
)tmp
where id between 11 and 15
总结: 这种方式采用 RowId BETWEEN 当前页数-1*页大小+1 and 页数*页大小 ,而且包含起始值与结束值。
2. order by [字段] offset [preIndex] rows fetch next [rowNum] rows only
select * from (
select row_number() over(order by createTime desc) as id, *
from test
) tmp
order by id offset 10 rows fetch next 5 rows only;
总结:这种方式的起始值与结束值计算方式: offset 页号*页大小 rows fetch next 页大小 rows only
3. row_number() over() + top
select top 5 * from (
select row_number() over(order by createTime desc) as id,
*
from test
) tmp
where tmp.id > 10
总结:第一个 top 后面的值相当于 页大小,第二个 id > 起始值,起始值计算方式为 (页号-1)*页大小