-- 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昵称为 死亡之旅 的笔记。

 ：