Oracle rownum原理和使用

版权声明：本文为博主原创文章，未经博主允许不得转载。

文章转自：http://blog.csdn.net/itmyhome1990/article/details/42492395

对于查询返回的每一行，使用rownum伪列返回一个数字，表示oracle从表中选择行或将加入行的顺序。

选择的第一行rownum为1，第二行为2，以此类推。

可以使用rownum来限制由查询返回的行数，如下例子：

[sql]  view plain  copy

 

1. select * from test where rownum < 10;  

如果一个order by子句和rownum在同一个查询，那么行会由order by子句中重新排序，看如下例子

[sql]  view plain  copy

 

1. select rownum, name, address, birthday  
2.   from test  
3.  where rownum < 5  
4.  order by birthday;  

我们发现rownum并不是顺序的，系统是按照记录插入的时候给记录排的号，解决这个问题 需要使用子查询

[sql]  view plain  copy

 

1. select rownum, name, address, birthday  
2.   from (select name, address, birthday from test order by birthday)  
3.  where rownum < 5  

这样就成了按birthday排序，并且用rownum标出正确序号(由小到大)

前面我们使用rownum<10查询出来了9条数据，这次我们使用rownum>1来查询一下数据

select * from test where rownum > 1;

发现该查询无返回任何数据，所以我们无法使用rownum = n 或 rownum > n(n>1的自然数)来进行查询

原因是：

第一行读取被分配为1，rownum>1使得条件为假，接着读取第二行现在变为第一行，并还分配为1

rownum使得条件依然是假，所有行随后均未能满足该条件，所以没有行被返回。

如果非要rownum大于一个正整数来查询，还得需要子查询来实现

[sql]  view plain  copy

 

1. select num, name, address, birthday  
2.   from (select rownum as num, name, address, birthday from test)  
3.  where num > 5;  

也可以使用between...and...

[sql]  view plain  copy

 

1. select num, name, address, birthday  
2.   from (select rownum as num, name, address, birthday from test)  
3.  where num between 5 and 10  

SQL测试脚本：

[sql]  view plain  copy

 

1. create table test(  
2.     name varchar2(100),  
3.     address varchar2(100),  
4.     birthday date  
5. );  
6. insert into test values('name1','address1',to_date('1990-04-10','yyyy-MM-dd'));  
7. insert into test values('name2','address2',to_date('1989-06-11','yyyy-MM-dd'));  
8. insert into test values('name3','address3',to_date('1991-08-23','yyyy-MM-dd'));  
9. insert into test values('name4','address4',to_date('1998-12-04','yyyy-MM-dd'));  
10. insert into test values('name5','address5',to_date('1985-07-16','yyyy-MM-dd'));  
11. insert into test values('name6','address6',to_date('1988-04-08','yyyy-MM-dd'));  
12. insert into test values('name7','address7',to_date('1992-08-26','yyyy-MM-dd'));  
13. insert into test values('name8','address8',to_date('1995-11-10','yyyy-MM-dd'));  
14. insert into test values('name9','address9',to_date('1988-03-28','yyyy-MM-dd'));  
15. insert into test values('name10','address10',to_date('1982-05-02','yyyy-MM-dd'));  
16. insert into test values('name11','address11',to_date('1991-10-19','yyyy-MM-dd'));  

参考文件

https://docs.Oracle.com/cd/E18283_01/server.112/e17118/pseudocolumns009.htm

作者：itmyhome