数据库分页操作

1、Oracle数据库：

A.select * from ( select a.*, rownum rn from (select * from table) a where rownum <= 40 ) where rn >= 21

B.select * from ( select a.*, rownum rn from (select * from table) a ) where rn between 21 and 40

C.select a.* from (select *,rownum rn from table where rownum <=5) a where rn >=3; 

例：select * from ( select t.*,rownum rn from ( select * from table) t where rownum<=(pageno*pagesize) )where rn>(paegno-1)*pagesize

2、MySQL数据库

A.select * from table limit 10,20

例：select * from table limit (pageno-1)*pageSize,pageno*pageSize

3、SqlServer数据库

A.select * from ( select *, row_number() over(order by id ) as id from table) as b where id between 10 and 20 

B.select b.* from (

select top 10 a.* from (

select top 20 * from table order by id asc ) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
as a order by a.id desc ) b order by b.id asc

C.select * from table order by id offset 4 rows fetch next 5 rows only（SQL2012以上的版本才支持：推荐使用 ）

例：select b.* from (select top (pageno-1)*pageSize a.* from (select top pageno*pageSize * from table order by id asc) a order by a.id desc ) b order by b.id asc