NOVOTS KMS 词汇表 Glossary    联系我们 Contact Us
查询 Search  
   
按类别浏览 Browse by Category
NOVOTS KMS .: 数据库 .: Oracle rownum用法总结

Oracle rownum用法总结

SQL> select rownum,empno,ename,sal from emp e order by sal;

    ROWNUM      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         1       7369 SMITH             800
        12       7900 JAMES             950
        11       7876 ADAMS            1100
         3       7521 WARD             1250
         5       7654 MARTIN           1250
        14       7934 MILLER           1300
        10       7844 TURNER           1500
         2       7499 ALLEN            1600
         7       7782 CLARK            2450
         6       7698 BLAKE            2850
         4       7566 JONES            2975

    ROWNUM      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         8       7788 SCOTT            3000
        13       7902 FORD             3000
         9       7839 KING             5000

已选择14行。
这个是所有员工按照sal排序的结果,后面查询的结果要和这个做对比,如果和这个顺序不符,说明结果是错的!!

第一个,是自然排序,也就是查出的结果和插入数据的顺序是一样的。
SQL> select rownum ,empno,ename,sal from emp e where rownum < 5;

    ROWNUM      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         1       7369 SMITH             800
         2       7499 ALLEN            1600
         3       7521 WARD             1250
         4       7566 JONES            2975

如果我们想按照sal排序,并且找出sal最低的前4个人:
SQL> select rownum,empno,ename,sal from emp e where rownum < 5 order by sal;

    ROWNUM      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         1       7369 SMITH             800
         3       7521 WARD             1250
         2       7499 ALLEN            1600
         4       7566 JONES            2975

我们发现,结果集按照sal排序,但是rownum的值是乱的。因为oracle先给rownum赋值,然后再按照order by排序。
而且:
排序的结果是不正确的!和第一个结果对照一下,JAMESsal900,但是这里并没有。
原因:
因为oracle先给rownum赋值,所以order by 是根据rownum<5的那4条记录排序

想要得到正确的结果,就要先排序,再给rownum赋值,可以嵌套查询:

select rownum,empno,ename,sal from
(select empno,ename,sal from emp e order by sal)
where rownum < 5;

    ROWNUM      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         1       7369 SMITH             800
         2       7900 JAMES             950
         3       7876 ADAMS            1100
         4       7521 WARD             1250

现在要查出按照sal排序,排名在510位之间的员工,如果这么写:

select rownum,empno,ename,sal from
(select empno,ename,sal from emp e order by sal)
where rownum > 5 and rownum <10;

结果:未选定行
为什么呢?rownum初始值是0,在查到一条记录后,rownum1,然后会判断是否满足条件,如果满足,放入结果集,如果不满足,
不放入结果集,rownum还原为原来的值。

查到第一条数据后,rownum=1,然后判断rownum > 5 and rownum <10,不满足条件,rownum =0;
查到第二条数据后,rownum=1,然后判断rownum > 5 and rownum <10,不满足条件,rownum =0;
查到第三条数据后,rownum=1,然后判断rownum > 5 and rownum <10,不满足条件,rownum =0;
.....
所以,没有查到任何结果。

解决的方法是再嵌套一层select
select * from
(
select rownum as no, empno,ename,sal from
(
   select empno,ename,sal from emp e order by sal
)
) r
where r.no > 5 and r.no <10;

        NO      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         6       7934 MILLER           1300
         7       7844 TURNER           1500
         8       7499 ALLEN            1600
         9       7782 CLARK            2450

与最前面的对照可知,结果是正确的。

除了oraclerownum之外,还有一个开创函数 row_number() over(),row_number(),可以少些一层select语句。
还是上面的要求,用row_number()来写的话:

select * from
(
select row_number() over(order by sal) no, empno,ename,sal from emp
) r
where r.no > 5 and r.no <10;

        NO      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         6       7934 MILLER           1300
         7       7844 TURNER           1500
         8       7499 ALLEN            1600
         9       7782 CLARK            2450

提醒1oracle在定义表别名的时候不能使用as,如果这么写,是不对的:

select * from
(
select row_number() over(order by sal) no, empno,ename,sal from emp
) as r
where r.no > 5 and r.no <10;

提醒2:在where后面不能出现列的别名,因为whereselect之前被解析! 所以下面的2种写法也是不对的。

1 select ename,sal as s from emp where s >1000;

2 select row_number() over(order by sal) no, empno,ename,sal from emp where no >5;

既然select row_number() over(order by sal) no, empno,ename,sal from emp where no >5; 既然where后面不能用别名,那我不用别名还不行吗?


我们可能会想到另外一种写法:
select row_number() over(order by sal) , empno,ename,sal from emp where row_number() over(order by sal) >5;

ORA-30483: window 函数在此禁用

提醒3where后面不能使用开窗函数

所以,如果用row_number()函数来查询m-n条记录时,也需要嵌套一层select语句。

 


这篇文章对你多有用?

相关文章

article EXP/IMP方法备份,恢复Oracle数据库及其优缺点
###############################################...

  12-21-2010    Views: 2493   
article [Oracle]Data Guard数据库灾难防护(容灾备份)
 Oracle9i Data Guard 通过使用称为standby...

(No rating)  3-30-2009    Views: 1951   
article Oracle 检查点队列与增量检查点
...

(No rating)  8-24-2012    Views: 918   

用户评语

添加评语
当前还没有评语.


.: .: .: .: .:
[ 登陆 ]
北京护航科技有限公司 2006

Novots Technologies Limited