SQL分页语句总结

---分页方法

------------1.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 

 

 ------------2.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 

 

-------------3.rowcount（）方法

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 

 

------------4.建立临时表

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