浅谈Oracle中Rownum的排序和比较

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


有的时候，我们经常碰见这样的需求(比如面试的时候...)，要求把一个结果集排序后，取得前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

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


最后，再来说一下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的解释：[url=http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/pseudocolumns008.htm]Here[/url]
以上是我与大家分享的一些拙见，希望能够帮助到像我这样的Oracle新人们，更好的理解Oracle Rownum～ 欢迎达人们指正～ :D