自我总结oracle分页查询的三种方法

以下都以查询第二页11-20条记录为例

1.两层子查询嵌套（不排序的话只用嵌套1层）

　　Select * from ( Select rownum rn,t.* from (Select * from user order by salary desc) t where rn <= 20 ) where rn > 10 ;

2.用分析函数row_number() over(XXX)实现分页查询

　　select * from (select row_number() over(order by salary desc) rn,u.* from user u) where rn <=20 and rn>10;

3 .利用差集进行分页查询

　 select rownum,t1.* from (select * from user order by salary desc) t1 where rownum <=20

　　minus

　select rownum,t2.* from (select * from user order by salary desc) t2 where rownum <=10;