Sql2005高效分页语句

 1、

select top 10 * from
  ( select top (@Page * 10) ROW_NUMBER() OVER (order by id) as RowNum, id, username    
    from Guest  where username = 'user'
  ) as T
where RowNum > ((@Page - 1) * 10)

 

2、

select  * from

( select ROW_NUMBER() OVER(order by id) as RowNum,id,username 

  from Guest  where username = 'user'

) as T

where RowNum between 31 and 60

 

3、

with T as

(select ROW_NUMBER() OVER(order by id) as RowNum,,id,username 

 from Guest  where username = 'user'

)

select * from T

where RowNum between 31 and 60

 

4、

SELECT  TOP 页大小 * FROM table1 WHERE id NOT IN ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDERBY id ) ORDER BY id 

 

5 、

SELECT TOP 页大小 *FROM table1 WHERE id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDERBY id ) A ) ORDER BY id