浅谈Oracle中Rownum的排序和比较

今天，在学习Oracle的时候，碰到了一点麻烦
后来，查阅了一些资料，做了点试验。
特此记录下来，与大家分享～
我文笔一般，说的都是大白话～希望大家能看的懂

有的时候，我们经常碰见这样的需求(比如面试的时候...)，要求把一个结果集排序后，取得前N条数据的值。
比如 取得某个部门中，工资最高的前10名。在SQLServer中可以通过Order by结合TOP就可以返回相应的数据。但是，在Oracle中没有top这个关键字。那么咱如何实现呢？
Oracle提供了一个rownum的伪列(pseudocolumn)，用于标明返回result的次序。比如返回的第一行数据的rownum为1，第2行为2...第N行为N。

首先，我们先建立这样一张表用来做试验：

SQL> CREATE TABLE TESTSORT(id NUMBER);
Table created


接下来，往这个表里INSERT 10条数据(ID从1到10)

SQL> declare
2    type int_table_type is table of int index by binary_integer;
3    int_table int_table_type;
4  
5  begin
6  
7    for i in 1..10 loop
8        int_table(i):=i;
9    end loop;
10    forall i in 1..int_table.count
11        insert into testsort values(int_table(i));
12  end;
13  /
PL/SQL procedure successfully completed


首先，我们先来看看，每个ID以及他们相应的ROWNUM。

SQL> select id,rownum from testsort order by id;
ID     ROWNUM
---------- ----------
1          1
2          2
3          3
4          4
5          5
6          6
7          7
8          8
9          9
10         10
10 rows selected


现在，我们来取一下 ID最小的前5个值

SQL> select id,rownum from testsort where rownum<=5 order by id;
ID     ROWNUM
---------- ----------
1          1
2          2
3          3
4          4
5          5


嗯嗯～看样子是正常显示了我们需要的数据。
不过先不要急，让我们再搞些“破坏”，执行下面的这几条SQL

SQL> delete from testsort where id in (1,2,3);
3 rows deleted
SQL> select id,rownum from testsort;
ID     ROWNUM
---------- ----------
4          1
5          2
6          3
7          4
8          5
9          6
10          7
7 rows selected
SQL> insert into testsort values(1);
1 row inserted
SQL> insert into testsort values(2);
1 row inserted
SQL> insert into testsort values(3);
1 row inserted
SQL> select id,rownum from testsort order by id;
ID     ROWNUM
---------- ----------
1          8
2          9
3         10
4          1
5          2
6          3
7          4
8          5
9          6
10          7
10 rows selected


我们再执行一下，之前那条SQL

SQL> select id,rownum from testsort where rownum<=5 order by id;
ID     ROWNUM
---------- ----------
4          1
5          2
6          3
7          4
8          5


结果是显而易见的，由于2次执行SQL的ROWNUM不一致，因此不能返回正常的数据。
那为什么会这样呢，我们需要理解ORACLE是如何给ROWNUM列赋值的。
Rownum是个伪列，并不是物理存在的，我的理解是先查到结果集之后再加上去的一个列。
那按照什么规则加呢？我们用cursor的观点去看的话，应该更容易理解。
“破坏”数据之前，fetch数据的顺序是 ID=1,2,3,4,5....
因此ID 1 的ROWNUM为1 ，ID 2的ROWNUM为2..以此类推。
所以查询的结果是我们所要的。
“破坏”数据之后，由于对TABLE做了DELETE，INSERT操作，导致fetch的顺序变更了
fetch顺序是 ID=4,5,6...1,2,3 也就是说ID4是最先被取回的数据，因此他的ROWNUM是1，ID5为2，ID1,2,3分别为8,9,10。完了之后，在Order by结果集，当然这个时候再用rownum<=5来过滤的数据就是错误的了。
这个“破坏”只是打个比喻。然而，在项目中对表的CRUD操作必不可少。所以说，像这样的SQL：select id from testsort where rownum<=5 order by id; 是一个陷阱。有一种情况例外，那就是ID为PK的时候，大家可以做下实验。
好了，下面该是如何解决问题的时候了。利用子查询问题就会游刃而解了

SQL> select t.id,rownum from (select * from testsort order by id) t where rownum<=5;
ID     ROWNUM
---------- ----------
1          1
2          2
3          3
4          4
5          5


嗯...from 子查询，返回的数据已经是被Order by了的数据了。自然fetch的顺序就是符合条件的。ORDER BY DESC也是一样的。

SQL> select t.id,rownum from (select * from testsort order by id desc) t where rownum<=5;
ID     ROWNUM
---------- ----------
10          1
9          2
8          3
7          4
6          5


虽然麻烦，但是貌似目前只能这样处理，如果哪位达人有更好的办法，请帮忙指点

最后，再来说一下Rownum比较操作符的问题。
很多资料都说不支持>，>=，=，between……and，只能用以上符号（<、& lt;=、！=）
虽说不支持，但并不会报Error。只是返回的数据永远为空，原因就是根本不可能满足这样的where条件。

SQL> select t.id,rownum from (select * from testsort order by id) t
where rownum > 5;
ID     ROWNUM
---------- ----------


之前说过，取回的第1条数据它的ROWNUM肯定是1。也就是说rownum肯定是从1开始顺番。
如上面这条SQL，取回第1条数据的id为1，rownum为1，然后Oracle一看，哦 1 < 5  ～不满足条件，舍弃了。然后取第2条数据id为2,rownum还是为1，又< 5，再舍弃。以此类推，最终舍弃了所有的数据，因此没有一条满足的，这就是所谓不支持的原因。当然一些符号（如：>=，=）和1比较是没有问题的。
那么怎么解决这个问题呢？比如，需要返回满足条件的 前2～4条数据？
我们可以举一反三，利用子查询依然能够解决这个问题。

--注意要给子查询中的rownum 起别名
select * from (
select t.id,rownum num from (select * from testsort order by id) t where rownum < 5) where num > 1;


最后这里是Oracle对Rownum的解释：Here
以上是我与大家分享的一些拙见，希望能够帮助到像我这样的Oracle新人们，更好的理解Oracle Rownum～ 欢迎达人们指正～