Sql数据分页原理

假定每页取10条数据，下面以取第3页数据为例(注意：一定要考虑自增id很可能不连续的情况)：

方法一：(id大于法——利用自增id大于前20条记录中的自增id最大者)

 select top 10 * 
 from Table_1 
 where id > ( 
    select max(id) 
        from ( select top 20 id 
               from Table_1 order by id
     ) as T
 ) 

/*通用写法，PageSize表示每页记录数，PageIndex表示当前页码(页码从1开始)*/ 
select top PageSize * 
 from Table_1 
 where id > ( 
    select max(id) 
        from ( select top (PageIndex - 1) * PageSize id 
               from Table_1 order by id
     ) as T
 )

方法二：(id "not in"法——利用自增id “not in”前20条记录中的所有自增id)

select top 10 *
from Table_1 
where id not in(
    select top 20 id 
    from Table_1
)

 方法三：(id颠倒法——利用自增id，先颠倒前30条记录，再取这30条记录中的前10条)

select top 10 * 
from (select top 30 *
      from Table_1 
      order by id  /*这里order by必须写上*/
) as T 
order by T.id desc  /*这里order by必须写上*/

/*将方法三的结果集转成以自增id顺序排列的形式*/
select * 
from(
    select top 10 * 
    from (select top 30 *
          from Table_1 
          order by id  /*这里order by必须写上*/
    ) as T 
    order by T.id desc  /*这里order by必须写上*/
) as TT 
order by TT.id

方法四：(利用ROW_NUMBER()函数)

select * 
from(
    select *, ROW_NUMBER() over (order by id) as rank
    from Table_1
) as T
where T.rank between 21 and 30

/*通用写法，PageSize表示每页记录数，PageIndex表示当前页码(页码从1开始)*/ 

select * 
from(
    select *, ROW_NUMBER() over (order by id) as rank
    from Table_1
) as T
where T.rank between ((PageIndex - 1) * PageSize) + 1 and PageIndex * PageSize

　　

方法五：(利用id "in"，和方法三有点类似只是绕了一点)

select top 10 * 
from Table_1  
where id in( 
    select top 10 id 
    from(  
        select top 30 id 
        from Table_1 
        order by id
    ) as T 
    order by T.id desc 
) 
order by id