ROWNUM 与ROW_NUMBER()OVER()

 

ROWNUM()函数是Oracle中所特有的，而ROW_NUMBER()OVER()是Oracle与SQLserver都支持的；

 

对于rownum和row_number()over()函数并没有太大差异。

row_number()函数的基本语法为：row_number() over(order by COLUMN_NAME) ，此函数还提供了排序的功能。

如果是在分页的场合下,我们首推row_number()over()函数。

我们已员工表为例子:

--查询(薪水从高到底的)员工信息

SELECT rownum,e.* FROM emp e ORDER BY sal;

结果:

--问题：为什么Order by以后行号(ROWNUM)是乱的。
--原因：先分配了行号(ROWNUM)，再根据sal排序。

分页:

<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

结果:

结果集为空!我们不难发现,ROWNUM必须从1开始！这是由rownum的特性(没有1就不会有2，没有3……以此类推下去),就像我一哥们说:没有父亲,哪来儿子！

问题：那么可能会有疑问,如何正确显示第二页的数据呢？

有两种方法:

第一种:以上的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;

结果:

两者在效率上没有任何差异，如果是对索引列进行排序的时候,row_number()over()函数有明显优势。