sql的几种分页方法
时间：2011-04-06 来源：YLWS
分页数：pagesize
页数：pageindex
举例：搜索第3页的前10条记录（21—30的记录）
1.top+not in
select top pagesize * from table where id not in(select top pagesize*(pageindex-1) id from table order by id) order by id
select top 10 * from table where id not in( select top 20 id from table order by id)order by id
2.top + max
select top pagesize * from table where id >(select max(id) from (select top pagesize*(pageindex-1) id from table order by id) as t) order by id
select top 10 * from table where id >( select max(id) from (select top 20 id from table order by id)as t)order by id
3.颠倒top原理：查询table中从m到n的记录，先查出前n条记录，然后将查询出来的记录按倒序排序，再取前n-m条记录，最后再将查出来的记录再按倒序排序，就是最终结果。
select * from (select top pagesize * from( select top pagesize*pageindex * from table order by id) as t1 order by id desc) as t2 order by idselect * from (select top 10 * from( select top 30 * from table order by id) as t1 order by id desc) as t2 order by id
总结：方案1由于有not效率比较低，方案2用的比较多，效率比较高，方案3当m很大的时候，效率低。
相关阅读 更多 +
