rownum，num_rows和row_number()的…

今天在看书的时候看到了row_number()函数，觉得有点眼熟，想起来之前在论坛上很多帖子里都看到有人使用rownum。一开始以为自己眼花看错了，去网上搜了一下，果然是两个不同的函数。搜了些内容，做了做实验，下面说一下自己对它们的理解。

 

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

在oracle中有rownum和row_number()函数能达到类似的效果，只是没有排序。但是row_number()也有存在的必要性，有些场合有使用的必要性。

 

我们看下面这个例子

1. SQL> select * from dept  
2.   2  where rownum<=3  
3.   3  order by deptno;  
4.   
5.     DEPTNO DNAME          LOC  
6. ---------- -------------- -------------  
7.         10 ACCOUNTING     NEW YORK  
8.         20 RESEARCH       DALLAS  
9.         30 SALES          CHICAGO  

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条件，查出第二到第三条记录。

 

1. SQL> select * from dept  
2.   2  where rownum between 2 and 3  
3.   3  order by deptno;  
4.   
5. no rows selected  

SQL> select * from dept 2 where rownum between 2 and 3 3 order by deptno; no rows selected

 

啊哦……出错了，没有返回值。这是由rownum的特性(没有1就不会有2，没有3……以此类推下去)决定的，相关内容可以查阅相关资料。

 

可以结合row_number() 函数来完成此功能。

1. SQL> select * from  
2.   2  (  
3.   3  select row_number() over(order by deptno) row_num,deptno,dname,loc  
4.   4  from dept  
5.   5  )a  
6.   6  where a.row_num between 2 and 3;  
7.   
8.    ROW_NUM     DEPTNO DNAME          LOC  
9. ---------- ---------- -------------- -------------  
10.          2         20 RESEARCH       DALLAS  
11.          3         30 SALES          CHICAGO  
12.   
13. SQL>  

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()在包含排序从句后是先排序再计算行号码。

 

1. SQL> select deptno,dname,rownum,row_number() over(order by dname) from dept orde  
2. r by dname;  
3.   
4.     DEPTNO DNAME              ROWNUM ROW_NUMBER()OVER(ORDERBYDNAME)  
5. ---------- -------------- ---------- ------------------------------  
6.         10 ACCOUNTING              1                              1  
7.         40 OPERATIONS              4                              2  
8.         20 RESEARCH                2                              3  
9.         30 SALES                   3                              4  
10.   
11. SQL>  

最后num_rows表示一个表里面一共有多少行数据，例子：

select * from user_tables where num_rows>3;

表示查询行数超过三的所有表