今天在看书的时候看到了row_number()函数,觉得有点眼熟,想起来之前在论坛上很多帖子里都看到有人使用rownum。一开始以为自己眼花看错了,去网上搜了一下,果然是两个不同的函数。搜了些内容,做了做实验,下面说一下自己对它们的理解。
row_number()函数的基本语法为:row_number() over(order by COLUMN_NAME) ,此函数还提供了排序的功能。
在oracle中有rownum和row_number()函数能达到类似的效果,只是没有排序。但是row_number()也有存在的必要性,有些场合有使用的必要性。
我们看下面这个例子
- SQL>
select * from dept -
2 where rownum<=3 -
3 order by deptno; -
-
DEPTNO DNAME LOC - ----------
-------------- ------------- -
10 ACCOUNTING NEW YORK -
20 RESEARCH DALLAS -
30 SALES CHICAGO
这个时候可能你想改变一下WHERE条件,查出第二到第三条记录。
- SQL>
select * from dept -
2 where rownum between 2 and 3 -
3 order by deptno; -
- no
rows selected
啊哦……出错了,没有返回值。这是由rownum的特性(没有1就不会有2,没有3……以此类推下去)决定的,相关内容可以查阅相关资料。
可以结合row_number() 函数来完成此功能。
- SQL>
select * from -
2 ( -
3 select row_number() over(order by deptno) row_num,deptno,dname,loc -
4 from dept -
5 )a -
6 where a.row_num between 2 and 3; -
-
ROW_NUM DEPTNO DNAME LOC - ----------
---------- -------------- ------------- -
2 20 RESEARCH DALLAS -
3 30 SALES CHICAGO -
- SQL>
还有另外一个区别,就是使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序,而函数row_number()在包含排序从句后是先排序再计算行号码。
- SQL>
select deptno,dname,rownum,row_number() over(order by dname) from dept orde - r
by dname; -
-
DEPTNO DNAME ROWNUM ROW_NUMBER()OVER(ORDERBYDNAME) - ----------
-------------- ---------- ------------------------------ -
10 ACCOUNTING 1 1 -
40 OPERATIONS 4 2 -
20 RESEARCH 2 3 -
30 SALES 3 4 -
- SQL>
最后num_rows表示一个表里面一共有多少行数据,例子:
select * from user_tables where num_rows>3;
表示查询行数超过三的所有表