oracle表中的伪列,Oracle数据库的伪列rownum的使用方法

1.伪列rownum

ROWNUM是一种伪列，它会根据返回记录生成一个序列化的数字。

比如，查询返回的结果集合，对于其中一条记录，我们可以说它是第几条记录，尽管这个结果集(表)中并没有一个用于显示这个数字几的列。有时候，我们想查看这里面的第1条到第5条的记录，由于没有标识第几行的属性，所以如果不借助伪列rownum，则无法做到这一点。

2.利用rownum来查询特定记录以及陷阱

ROWNUM是一个伪列，只有有结果记录时，ROWNUM才有相应数据，因此对它的使用不能向普通列那样使用，否则就会陷入一些“陷阱”当中。

不能对ROWNUM使用>(大于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;