sql server 分页

-- not in/top

select top 1000 * from SysACUser
where UserId not in (select top 1 UserId from SysACUser order by UserId)
order by UserId

-- not exists

select top 1000 * from SysACUser as a
where not exists (select 1 from ( select top 1000 UserId from SysACUser order by UserId ) as b where a.UserId
= b.UserId)
order by UserId

-- max top
select top 1000 * from SysACUser
where UserId > (select MAX(userid) from (select top 1000 UserId from SysACUser order by UserId) as a)
order by UserId

--row_number

select top 1000 * from (select ROW_NUMBER() over(order by UserId) rownumber , * from SysACUser) as a
where a.rownumber > 1000

select * from (select ROW_NUMBER() over(order by UserId) rownumber , * from SysACUser) as a
where a.rownumber > 1000 and a.rownumber <=2000

select * from (select ROW_NUMBER() over(order by UserId) rownumber , * from SysACUser) as a
where a.rownumber between 1001 and 2000

-- row_number() 变体， 不基于已有字段产生记录序号，先按 条件筛选排好序，再结果集上给一常量列用于产生记录序号。 效率最高
select * from(
select ROW_NUMBER() over(order by temp) rownumber,* from(
select top 4235 temp=0,* from SysACUser where 1=1 order by UserId
) as a
)as b
where rownumber > 3235

-- Offset-Fetch

--必须为 order by 子句 可以写为 order by(select 1) 

-- order by (select 1) 的意思为 SQL Server按照结果集的原始顺序返回，order by子句不对结果集排序
select * from SysACUser
order by (select 1)
offset 1001 rows
fetch next 1000 rows only

 

效率上来看  Offset-Fetch 最快 。

参考：QQ昵称为 死亡之旅 的笔记。

悦光阴 ：https://www.cnblogs.com/ljhdo/p/4861263.html