row_number()和rownum的区别

ROWNUM必须从1开始！这是由rownum的特性(没有1就不会有2，没有3……以此类推下去)

<1>第一页数据

SELECT ROWNUM ,t.* FROM ( 
   SELECT e.* FROM emp e ORDER BY e.sal DESC  ) t 
WHERE ROWNUM between 1 AND 3

结果: 

<2>第二页数据

SELECT ROWNUM ,t.* FROM ( 
   SELECT e.* FROM emp e ORDER BY e.sal DESC  ) t 
WHERE ROWNUM between 4 AND 6

结果:

如何正确显示第二页的数据呢？

有两种方法:

第一种:以上的sql语句再嵌套一个SELECT语句

SELECT k.* FROM (
　　SELECT ROWNUM r,t.* FROM ( 
  　　 SELECT e.* FROM emp e ORDER BY e.sal DESC  ) t 
 ) k
WHERE r between 4 AND 6

结果:

第二种:使用ROW_NUMBER()OVER()函数

select t2.* 
  from (select t.*, row_number() over(order by t.sal desc) orderNumber 
          from emp t 
        ) t2 
where orderNumber between 4 and 6;

结果: