rownum 伪列的用法

- 日理万妓 2022-07-12 14:16 191阅读 0赞

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。

(1) rownum 对于等于某值的查询条件  
如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。  
SQL> select rownum,id,name from student where rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标）  
SQL> select rownum,id,name from student where rownum =2;  
ROWNUM ID NAME  
\---------- ------ ---------------------------------------------------

（2）rownum对于大于某值的查询条件  
如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录。

查找到第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。  
SQL>select \* from(select rownum no ,id,name from student) where no>2;  
NO ID NAME  
\---------- ------ ---------------------------------------------------  
3 200003 李三  
4 200004 赵四

（3）rownum对于小于某值的查询条件  
rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。  
SQL> select rownum,id,name from student where rownum <3;  
ROWNUM ID NAME  
\---------- ------ ---------------------------------------------------  
1 200001 张一  
2 200002 王二

查询rownum在某区间的数据，必须使用子查询。例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句，先让它返回小于等于三的记录行，然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。  
SQL> select \* from (select rownum no,id,name from student where rownum<=3 ) where no >=2;  
NO ID NAME  
\---------- ------ ---------------------------------------------------  
2 200002 王二  
3 200003 李三

（4）rownum和排序  
Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。  
SQL> select rownum ,id,name from student order by name;  
ROWNUM ID NAME  
\---------- ------ ---------------------------------------------------  
3 200003 李三  
2 200002 王二  
1 200001 张一  
4 200004 赵四  
可以看出，rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。为了解决这个问题，必须使用子查询；  
SQL> select rownum ,id,name from (select \* from student order by name);  
ROWNUM ID NAME  
\---------- ------ ---------------------------------------------------  
1 200003 李三  
2 200002 王二  
3 200001 张一  
4 200004 赵四  
这样就成了按name排序，并且用rownum标出正确序号（有小到大）

笔者在工作中有一上百万条记录的表，在jsp页面中需对该表进行分页显示，便考虑用rownum来作，下面是具体方法(每页显示20条)：  
“select \* from tabname where rownum<20 order by name" 但却发现oracle却不能按自己的意愿来执行，而是先随便取20条记录，然后再order by，后经咨询oracle,说rownum确实就这样，想用的话，只能用子查询来实现先排序，后rownum，方法如下：  
"select \* from (select \* from tabname order by name) where rownum<20",但这样一来，效率会低很多。  
后经笔者试验，只需在order by 的字段上加主键或索引即可让oracle先按该字段排序，然后再rownum；方法不变：“select \* from tabname where rownum<20 order by name"

以下摘自《中国IT实验室》

1.在ORACLE中实现SELECT TOP N

由于ORACLE不支持SELECT TOP语句，所以在ORACLE中经常是用ORDER BY跟ROWNUM的组合来实现SELECT TOP N的查询。

简单地说，实现方法如下所示：

SELECT　列名１．．．列名ｎ　FROM

(SELECT　列名１．．．列名ｎ　FROM 表名 ORDER BY 列名１．．．列名ｎ)

WHERE ROWNUM <= N（抽出记录数）

ORDER BY ROWNUM ASC

下面举个例子简单说明一下。

顾客表customer(id,name)有如下数据：

ID NAME

01 first

02 Second

03 third

04 forth

05 fifth

06 sixth

07 seventh

08 eighth

09 ninth

10 tenth

11 last

则按NAME的字母顺抽出前三个顾客的SQL语句如下所示：

SELECT \* FROM

(SELECT \* FROM CUSTOMER ORDER BY NAME)

WHERE ROWNUM <= 3

ORDER BY ROWNUM ASC

输出结果为：

ID NAME

08 eighth

05 fifth

01 first

2.在TOP N纪录中抽出第M（M <= N）条记录

ROWNUM是记录表中数据编号的一个隐藏子段，所以可以在得到TOP N条记录的时候同时抽出记录的ROWNUM，然后再从这N条记录中抽取记录编号为M的记录，即使我们希望得到的结果。

从上面的分析可以很容易得到下面的SQL语句。

SELECT 列名１．．．列名ｎ FROM

(

SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM

(SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)

WHERE ROWNUM <= N（抽出记录数）

ORDER BY ROWNUM ASC

)

WHERE RECNO = M（M <= N）

同样以上表的数据为基础，那么得到以NAME的字母顺排序的第二个顾客的信息的SQL语句应该这样写：

SELECT ID, NAME FROM

(

SELECT ROWNUM RECNO, ID, NAME FROM

(SELECT \* FROM CUSTOMER ORDER BY NAME)

WHERE ROWNUM <= 3

ORDER BY ROWNUM ASC )

WHERE RECNO = 2

结果则为：

ID NAME

05 fifth

3.抽出按某种方式排序的记录集中的第N条记录

在2的说明中，当M = N的时候，即为我们的标题讲的结果。实际上，2的做法在里面N>M的部分的数据是基本上不会用到的，我们仅仅是为了说明方便而采用。

如上所述，则SQL语句应为：

SELECT 列名１．．．列名ｎ FROM

(

SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM

(SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)

WHERE ROWNUM <= N（抽出记录数）

ORDER BY ROWNUM ASC

)

WHERE RECNO = N

那么，2中的例子的SQL语句则为：

SELECT ID, NAME FROM

(

SELECT ROWNUM RECNO, ID, NAME FROM

(SELECT \* FROM CUSTOMER ORDER BY NAME)

WHERE ROWNUM <= 2

ORDER BY ROWNUM ASC

)

WHERE RECNO = 2

结果为：

ID NAME

05 fifth

4.抽出按某种方式排序的记录集中的第M条记录开始的X条记录

3里所讲得仅仅是抽取一条记录的情况，当我们需要抽取多条记录的时候，此时在2中的N的取值应该是在N >= (M + X - 1)这个范围内，当让最经济的取值就是取等好的时候了的时候了。当然最后的抽取条件也不是RECNO = N了，应该是RECNO BETWEEN M AND (M + X - 1)了，所以随之而来的SQL语句则为：

SELECT 列名１．．．列名ｎ FROM

(

SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM

(

SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)

WHERE ROWNUM <= N （N >= (M + X - 1)）

ORDER BY ROWNUM ASC

)

WHERE RECNO BETWEEN M AND (M + X - 1)

同样以上面的数据为例，则抽取NAME的字母顺的第2条记录开始的3条记录的SQL语句为：

SELECT ID, NAME FROM

(