今天对分页语句做一个简单的总结，他们大同小异的，只要理解其中一个其他的就很好理解了。
使用top选项
select top 10 * from Orders
a where a.orderid not in(select top 10 orderid from Orders order by orderid) order by a.orderid
使用max函数
这种方法的前提是有唯一值的一个列。
select top 10 * from Orders
a where a.orderid>(select MAX(orderid) from (select top 10 orderid from Orders order by orderid) as orderid)
order by orderid
使用row_number()
select * from (select ROW_NUMBER() over(order by orderid) as rownumber,* from Orders) myresult
where rownumber between 10 and 20
select top 10 * from (select ROW_NUMBER() over(order by orderid) as rownumber,* from Orders) myresult
where rownumber>10
with OrderedResult as(
select *,ROW_NUMBER() over(Order by orderid)
as rownumber from Orders)select * from OrderedResult where rownumber between 10 and 20
使用rowcount设置
begin
declare @first_id varchar(18),@startrow int
set rowcount 10
select @first_id=orderid from Orders order by orderid
select * from Orders where orderid>@first_id order by orderid
set rowcount 0
end
使用临时表
begin
declare @pagelowerbound int
declare @pageupperbound int
set @pagelowerbound=10
set @pageupperbound=20
create table #pageindex([indexid] int identity(1,1) not null,[id] varchar(18))
declare @sql nvarchar(2000)
set @sql=insert into #pageindex([id]) select top +CONVERT(nvarchar,@pageupperbound)
set @sql=@sql+ orderid from Orders
execute sp_executesql @sql
select a.* from Orders a inner join #pageindex b on a.orderid=b.id where b.indexid>@pagelowerbound order by b.indexid
drop table #pageindex
end