本文内容及示例代码参考《 Oracle中ROWNUM的使用技巧。》
1.伪列rownum
ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。
比如，查询返回的结果集合，对于其中一条记录，我们可以说它是第几条记录，尽管这个结果集（表）中并没有一个用于显示这个数字几的列。有时候，我们想查看这里面的第1条到第5条的记录，由于没有标识第几行的属性，所以如果不借助伪列rownum，则无法做到这一点。
2.利用rownum来查询特定记录以及陷阱
ROWNUM是一个伪列，只有有结果记录时，ROWNUM才有相应数据，因此对它的使用不能向普通列那样使用，否则就会陷入一些“陷阱”当中。
- 不能对
ROWNUM使用>（大于1的数值）、>=（大于或等于1的数值）、=（大于或等于1的数值），否则无结果。但可以用<等，因为它是从1开始计数：
SQL> select * from dept where rownum<=2;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
SQL> select * from dept where rownum<3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
SQL> select * from dept where rownum>1;
未选定行
这是因为：
-
ROWNUM是伪列，必须要要有返回结果后，每条返回记录就会对应产生一个ROWNUM数值； - 返回结果记录的
ROWNUM是从1开始排序的，因此第一条始终是1;
这样，当查询到第一条记录时，该记录的
ROWNUM为1，但条件要求ROWNUM>1，因此不符合，继续查询下一条；因为前面没有符合要求的记录，因此下一条记录过来后，其ROWNUM还是为1，如此循环，就不会产生结果。
这就是说，查询时遍历表，选定一个记录，为其分配一个行数也就是rownum=i。当记录符合where要求时加入结果集，此时，rownum产生器再自增变成i+1；否则，不加入结果集，则产生器自减又回到i，为下一条符合条件的记录分配。
当第一个结果去验证where时候，这个记录的rownum是1，但是它不满足>1的条件，则不加入结果集，因此下一条结果过来时，它的rownum还是1而不是2（即产生器自减了），它当然也不符合>1条件……如此往复，不可能有结果集合。
因为rownum动态可变，所以它是伪列
- 使用子查询来完成上面结果
SQL> select * from(
2 select dept.*,rownum as rn from dept
3 ) where rn>1;
DEPTNO DNAME LOC RN
---------- ---------------------------- -------------------------- ----------
20 RESEARCH DALLAS 2
30 SALES CHICAGO 3
40 OPERATIONS BOSTON 4
注意到，上面在内层的select中加入了rownum，是之固定为一个“真列”，当然必须要起一个别名，因为rownum是关键字，在外部使用别名进行筛选就好了。另外，不能写成from( select *.rownum as rn)...，通配符前面的表面是必要的。
当然了，如果想查询如大于等于第三行的记录，可以使用差集运算minus，先求出总集合，再减去小于第三行的记录即可：
SQL> select * from dept
2 minus
3 select * from dept where rownum<3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
30 SALES CHICAGO
40 OPERATIONS BOSTON
-
ROWNUM和Order BY
在使用ROWNUM时，只有当Order By的字段是主键时，查询结果才会先排序再计算ROWNUM。
例如，在dept表中，deptno是主键：
SQL> select dept.*,rownum
2 from dept
3 where rownum<3
4 order by deptno; /*主键排序*/
DEPTNO DNAME LOC ROWNUM
---------- ---------------------------- -------------------------- ----------
10 ACCOUNTING NEW YORK 1
20 RESEARCH DALLAS 2
SQL> select dept.*,rownum
2 from dept
3 where rownum<3
4 order by loc; /*非主键排序*/
DEPTNO DNAME LOC ROWNUM
---------- ---------------------------- -------------------------- ----------
20 RESEARCH DALLAS 2
10 ACCOUNTING NEW YORK 1
出现这种混乱的原因是：oracle先按物理存储位置顺序（rownum）取出物理位置上的前2条数据，然后再对这些数据按照Order By的字段进行排序，而不是我们所期望的先排序、再取特定记录数。
解决方案是：
SQL> select * from(
2 select * from dept
3 order by loc
4 )
5 where rownum<3;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
40 OPERATIONS BOSTON
30 SALES CHICAGO
3.如下三个查询的结果是相同的，但是性能是由差到好的：
-
差 :
/ROWNUM对结果进行分页，下面返回结果中的第6到第10条记录/
select * from(
select contacts*, rownum as rn
from contacts
where phone = '(1) 925-4604800'
) res
where res.rn between 6 and 10;
```
-
中：
select * from contacts
where phone = '(1) 925-4604800'
and rownum <= 10
MINUS /求集合差/
select * from contacts
where phone = '(1) 925-4604800'
and rownum <= 5
;
```
-
好：
select * from(
select contacts.*, rownum as rn
from contacts
where phone = '(1) 925-4604800' AND rownum <= 10
) res where res.rn > 5;
```
4.分组标号
设有一个亲子关系的parentage表：
SQL> select * from parentage;
FATHER CHILD
---------- ----------
David Cindy
David Tomy
Tom Amy
Tom Bob
Tom Nick
主码是全码。
下面的语句：
SELECT DECODE(a.rn-min_sno,0,a.father,NULL) father,
DECODE(a.rn-min_sno,0,1,a.rn+1-min_sno) sno,
a.child
FROM (
SELECT parentage.*,rownum as rn
FROM parentage
ORDER BY father, child
) a,
(
SELECT father, MIN(rownum) min_sno
FROM(
SELECT *
FROM parentage
ORDER BY father, child
)
GROUP BY father
) b
WHERE a.father=b.father;
将会输出：
FATHER SNO CHILD
---------- ---------- ----------
Tom 1 Amy
2 Bob
3 Nick
David 1 Cindy
2 Tomy
特别注意：条件判断函数decode语句
decode( A, B1,C1,B2,C2,....,Bn-1,Cn-1,Cn)
等价于如下的逻辑代码：
if（A==B1):
return C1;
else if(A==B2):
return C2;
....
else:
return Cn;
网友评论