ITPub博客

首页 > 数据库 > Oracle > ORACLE中ROWNUM的说明

ORACLE中ROWNUM的说明

Oracle 作者:gaoyiwei5 时间:2012-06-01 15:16:08 0 删除 编辑
在Oracle中,要按照特定条件查询前N条记录,可以用rownum就搞定了
select * from emp where rownum <= 5;
但是不能对rownum使用>,也就是说以下的查询语句是无效的:
select * from emp where rownum > 5;
但是这条语句为什么不能够使用呢?这雨rownum背后的机制有关系:
1、Oracle executes your query
2、Oracle fetches the first row and calls it now number 1
3、Have we gotten past row number meets the criteria? If no, then Oracle discards the row, if yes, then oracle return the row
4、Oracle fetches the next and advances the row number(to 2,and then to 3, and then to 4, and so forth)
5、Go to step 3
Oracle在rownum的处理上,rownum是在得到结果集的时候产生的,用于标记结果集中结果顺序的一个字段,这个字段称为伪数列,也就是事实上不存在这么一个数列。它的特点是按顺序标记,而且是逐次递加的。
了解了原理,就知道rownum>是不会成功的,因为第三步的时候查询出来的行已经被丢弃,第四步查出来的rownum仍然是1,这样永远是不会成功的。
同样的道理,rownum如果单独使用,也只有rownum=1的时候才有用

对于rownum来说它是Oracle系统顺序分配为查询返回的行的编号,返回的第一行分配的是1,第二行是2,以此类推,这个伪字段可以用于限制查询返回的总行数,而且rownum不能以任何表的名称作为前缀
这里仍然以emp表为例:
SQL> select rownum,emp.* from emp order by empno;
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
         1       7369 SMITH      CLERK           7902 1980/12/17        800                    20
         2       7499 ALLEN      SALESMAN        7698 1981/02/20       1600        300         30
         3       7521 WARD       SALESMAN        7698 1981/02/22       1250        500         30
         4       7566 JONES      MANAGER         7839 1981/04/02       2975                    20
         5       7654 MARTIN     SALESMAN        7698 1981/09/28       1250       1400         30
         6       7698 BLAKE      MANAGER         7839 1981/05/01       2850                    30
         7       7782 CLARK      MANAGER         7839 1981/06/09       2450                    10
         8       7788 SCOTT      ANALYST         7566 1987/04/19       3500                    20
         9       7839 KING       PRESIDENT            1981/11/17       5000                    10
        10       7844 TURNER     SALESMAN        7698 1981/09/08       1500          0         30
        11       7876 ADAMS      CLERK           7788 1987/05/23       1100                    20
        12       7900 JAMES      CLERK           7698 1981/12/03        950                    30
        13       7902 FORD       ANALYST         7566 1981/12/03       3000                    20
        14       7934 MILLER     CLERK           7782 1982/01/23       1300                    10
已选择14行。

1、rownum对等于某值的查询条件
如果希望找到员工中第一条员工的信息,可以使用rownum=1的查询条件。但是如果想找到学生表中第二条员工的信息,使用rownum=2是查询不到数据的。因为rownum都是从1开始的,但是1以上的自然数在rownum做等值判断时都认为为false条件,所以无法查询到rownum=n(n>1的自然数)
SQL> select rownum,emp.* from emp where rownum=1 order by empno;
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
         1       7369 SMITH      CLERK           7902 1980/12/17        800                    20

SQL> select rownum,emp.* from emp where rownum=2 order by empno;
未选定行
SQL>

2、rownum对于大于某值的查询条件
如果想找到从第二行记录以后的记录,使用rownum>2是查询不出记录的,原因是由于rownum是一个总是从1开始的伪列,Oracle认为rownum>n(n>1的自然数)这种条件依旧不成立,所以查询不到记录
SQL> select rownum,emp.* from emp where rownum>2 order by empno;
未选定行
SQL>
那么该如何查询到第二行以后的记录呢?可以通过子查询的方式来实现
SQL> select rownum,a.* from (select rownum no,emp.* from emp) a where no>2 order by empno;
    ROWNUM         NO      EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
         1          3       7521 WARD       SALESMAN        7698 1981/02/22       1250        500         30
         2          4       7566 JONES      MANAGER         7839 1981/04/02       2975                    20
         3          5       7654 MARTIN     SALESMAN        7698 1981/09/28       1250       1400         30
         4          6       7698 BLAKE      MANAGER         7839 1981/05/01       2850                    30
         5          7       7782 CLARK      MANAGER         7839 1981/06/09       2450                    10
         6          8       7788 SCOTT      ANALYST         7566 1987/04/19       3500                    20
         7          9       7839 KING       PRESIDENT            1981/11/17       5000                    10
         8         10       7844 TURNER     SALESMAN        7698 1981/09/08       1500          0         30
         9         11       7876 ADAMS      CLERK           7788 1987/05/23       1100                    20
        10         12       7900 JAMES      CLERK           7698 1981/12/03        950                    30
        11         13       7902 FORD       ANALYST         7566 1981/12/03       3000                    20
        12         14       7934 MILLER     CLERK           7782 1982/01/23       1300                    10
已选择12行。

3、rownum对于小于某值的查询条件
如何想找到第三条记录以前的记录,当使用rownum<3的时候是只能得到两条记录的,然而rownum对于rownum1的自然数)的条件认为是成立的,所以可以找到记录:
SQL> select rownum,emp.* from emp where rownum<3 order by empno;
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
         1       7369 SMITH      CLERK           7902 1980/12/17        800                    20
         2       7499 ALLEN      SALESMAN        7698 1981/02/20       1600        300         30
已选择2行。
综上的几种情况,可能有时候需要查询rownum所在的某个区间内的数据,从上面可以看出rownum对于小于某值的查询条件是认为true的,rownum对于大于某值的查询条件是直接认为false的,但是可以通过间接的方式将其转换为true状态。
那就必须使用使用子查询了。
例如要查询rownum在第二行到第三行之间的记录,包括第二行和第三行数据,那么我们只能写下面的语句:
SQL> select * from (select rownum no,emp.* from emp where rownum <= 3 order by empno) a where no >= 2;
        NO      EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
         2       7499 ALLEN      SALESMAN        7698 1981/02/20       1600        300         30
         3       7521 WARD       SALESMAN        7698 1981/02/22       1250        500         30
已选择2行。
SQL>
4、rownum和排序
Oracle中的rownum是在获取数据的时候产生的序号,所以相对指定排序的数据去指定rownum行数据就必须注意了
SQL> select rownum,emp.* from emp order by ename;
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
        11       7876 ADAMS      CLERK           7788 1987/05/23       1100                    20
         2       7499 ALLEN      SALESMAN        7698 1981/02/20       1600        300         30
         6       7698 BLAKE      MANAGER         7839 1981/05/01       2850                    30
         7       7782 CLARK      MANAGER         7839 1981/06/09       2450                    10
        13       7902 FORD       ANALYST         7566 1981/12/03       3000                    20
        12       7900 JAMES      CLERK           7698 1981/12/03        950                    30
         4       7566 JONES      MANAGER         7839 1981/04/02       2975                    20
         9       7839 KING       PRESIDENT            1981/11/17       5000                    10
         5       7654 MARTIN     SALESMAN        7698 1981/09/28       1250       1400         30
        14       7934 MILLER     CLERK           7782 1982/01/23       1300                    10
         8       7788 SCOTT      ANALYST         7566 1987/04/19       3500                    20
         1       7369 SMITH      CLERK           7902 1980/12/17        800                    20
        10       7844 TURNER     SALESMAN        7698 1981/09/08       1500          0         30
         3       7521 WARD       SALESMAN        7698 1981/02/22       1250        500         30
已选择14行。
可以看出,rownum并不是按照ename列来生产的序号,而是按照系统插入记录的先后顺序给记录编的号,rowid也是顺序分配的,为了解决这个问题,必须使用子查询:
SQL> select rownum,a.* from (select * from emp order by ename) a;
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
         1       7876 ADAMS      CLERK           7788 1987/05/23       1100                    20
         2       7499 ALLEN      SALESMAN        7698 1981/02/20       1600        300         30
         3       7698 BLAKE      MANAGER         7839 1981/05/01       2850                    30
         4       7782 CLARK      MANAGER         7839 1981/06/09       2450                    10
         5       7902 FORD       ANALYST         7566 1981/12/03       3000                    20
         6       7900 JAMES      CLERK           7698 1981/12/03        950                    30
         7       7566 JONES      MANAGER         7839 1981/04/02       2975                    20
         8       7839 KING       PRESIDENT            1981/11/17       5000                    10
         9       7654 MARTIN     SALESMAN        7698 1981/09/28       1250       1400         30
        10       7934 MILLER     CLERK           7782 1982/01/23       1300                    10
        11       7788 SCOTT      ANALYST         7566 1987/04/19       3500                    20
        12       7369 SMITH      CLERK           7902 1980/12/17        800                    20
        13       7844 TURNER     SALESMAN        7698 1981/09/08       1500          0         30
        14       7521 WARD       SALESMAN        7698 1981/02/22       1250        500         30
已选择14行。
这样就成了按照name排序,并且使用rownum标出了需要的序号序列(由小到大)

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22939009/viewspace-1117580/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-11-27