oracle分页

oracle分页

---

方法1：

--嵌套笨办法
select rowns.* from
   (select orders.*, rownum as rn from
        (select r.* 
         from prpd_sysrole r 
         where 1=1 
         and  r.INSERTTIME between to_date('2016/1/1', 'yyyy/mm/dd') AND to_date('2019/12/12', 'yyyy/mm/dd') 
         order by r.INSERTTIME DESC
        ) orders
    ) rowns        
where rn between 2 and 4

执行计划

方法2：

select rowns.* from
   (select rownum as rn, orders.* from
        (select r.* 
         from prpd_sysrole r 
         where 1=1 
         and  r.INSERTTIME between to_date('2016/1/1', 'yyyy/mm/dd') AND to_date('2019/12/12', 'yyyy/mm/dd') 
         order by r.INSERTTIME DESC
        ) orders
   where ROWNUM <=4
   ) rowns        
where rowns.rn >= 2

执行计划

oracle使用伪列rownum分页

SELECT * FROM( SELECT * FROM emp ORDER BY sal DESC) WHERE Rownum=5

结果查不到结果，同样ROWNUM>=5查询5名之后的成绩也失败，因为ROWNUM是一个每一次都要从1开始排列的伪列，例如取第一条数据到缓冲区 rownum=1不符合条件，于是便扔掉这条数据，再取第二条数据的rownum还是为1，则后来取得数据rownum总是为1，查不到数据！

转载于:https://my.oschina.net/depeng414/blog/3085391