oracle中常用两种分页方式

--oracle的两种常用分页方式
--a.利用伪列rownum分页获取第6-10条记录
select *  from (select rownum rn,e.* from (select * from emp order by empno) e where rownum<11) where rn>5;

--思路：
1.select * from emp order by empno; --查询emp表并让结果按empno排序显示

2.select rownum rn,e.* from (select * from emp order by empno) e where rownum<11; --显示rownum ,将1中的查询结果作为表（内嵌视图）查询前10条记录

3.select *  from (select rownum rn,e.* from (select * from emp order by empno) e where rownum<11) where rn>5; --查询2中的结果（1-10条记录）的6-10条记录


--b.rowid和rownum结合分页方式获取第6-10条记录（比上面的方法高效些，但是书写相对麻烦，较为复杂）
select * from emp where rowid in (select rid from (select rownum rn,rid from (select rowid rid from emp order by empno) where rownum<11) where rn>5) order by empno;

思路：
1.select rowid from emp order by empno;--显示rowid 并让结果按empno排序显示


2.select rownum rn,rid from (select rowid rid from emp order by empno) where rownum<11;--显示rownum ,将1中的查询结果作为表（内嵌视图）查询前10条记录

3.select rid from (select rownum rn,rid from (select rowid rid from emp order by empno) where rownum<11) where rn>5;--查询2中的结果（1-10条记录）的6-10条记录的rid

4.select * from emp where rowid in (select rid from (select rownum rn,rid from (select rowid rid from emp order by empno) where rownum<11) where rn>5) order by empno;
--查询emp中rid在第3步的查询结果范围的数据并且按empno排序显示[size=medium][/size]