SQL分页语句总结

select   top   10   *   from  Orders

-- -这种方法的前提是有唯一值的一个列。
select   top   10   *   from  Orders a 
  where  a.orderid > ( select   MAX (orderid)  from  ( select   top   10  orderid  from  Orders  order   by  orderid)  as  orderid) 

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)

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