Oracle數(shù)據(jù)庫的偽列rownum的使用方法

本文內(nèi)容及示例代碼參考《 Oracle中ROWNUM的使用技巧。》

1.偽列rownum

ROWNUM是一種偽列，它會根據(jù)返回記錄生成一個序列化的數(shù)字。

比如，查詢返回的結(jié)果集合，對于其中一條記錄，我們可以說它是第幾條記錄，盡管這個結(jié)果集（表）中并沒有一個用于顯示這個數(shù)字幾的列。有時候，我們想查看這里面的第1條到第5條的記錄，由于沒有標(biāo)識第幾行的屬性，所以如果不借助偽列rownum，則無法做到這一點(diǎn)。

2.利用rownum來查詢特定記錄以及陷阱

ROWNUM是一個偽列，只有有結(jié)果記錄時，ROWNUM才有相應(yīng)數(shù)據(jù)，因此對它的使用不能向普通列那樣使用，否則就會陷入一些“陷阱”當(dāng)中。

  • 不能對ROWNUM使用>（大于1的數(shù)值）、>=（大于或等于1的數(shù)值）、=（大于或等于1的數(shù)值），否則無結(jié)果。但可以用<等，因為它是從1開始計數(shù)：
SQL> select * from dept where rownum<=2;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS

SQL> select * from dept where rownum<3;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS

SQL> select * from dept where rownum>1;

未選定行

這是因為：

  1. ROWNUM是偽列，必須要要有返回結(jié)果后，每條返回記錄就會對應(yīng)產(chǎn)生一個ROWNUM數(shù)值；
  2. 返回結(jié)果記錄的ROWNUM是從1開始排序的，因此第一條始終是1;

這樣，當(dāng)查詢到第一條記錄時，該記錄的ROWNUM為1，但條件要求ROWNUM>1，因此不符合，繼續(xù)查詢下一條；因為前面沒有符合要求的記錄，因此下一條記錄過來后，其ROWNUM還是為1，如此循環(huán)，就不會產(chǎn)生結(jié)果。

這就是說，查詢時遍歷表，選定一個記錄，為其分配一個行數(shù)也就是rownum=i。當(dāng)記錄符合where要求時加入結(jié)果集，此時，rownum產(chǎn)生器再自增變成i+1；否則，不加入結(jié)果集，則產(chǎn)生器自減又回到i，為下一條符合條件的記錄分配。
當(dāng)?shù)谝粋€結(jié)果去驗證where時候，這個記錄的rownum是1，但是它不滿足>1的條件，則不加入結(jié)果集，因此下一條結(jié)果過來時，它的rownum還是1而不是2（即產(chǎn)生器自減了），它當(dāng)然也不符合>1條件……如此往復(fù)，不可能有結(jié)果集合。
因為rownum動態(tài)可變，所以它是偽列

  • 使用子查詢來完成上面結(jié)果
SQL> select * from(
  2      select dept.*,rownum as rn from dept
  3  ) where rn>1;

    DEPTNO DNAME                        LOC                                RN
---------- ---------------------------- -------------------------- ----------
        20 RESEARCH                     DALLAS                              2
        30 SALES                        CHICAGO                             3
        40 OPERATIONS                   BOSTON                              4

注意到，上面在內(nèi)層的select中加入了rownum，是之固定為一個“真列”，當(dāng)然必須要起一個別名，因為rownum是關(guān)鍵字，在外部使用別名進(jìn)行篩選就好了。另外，不能寫成from( select *.rownum as rn)...，通配符前面的表面是必要的。
當(dāng)然了，如果想查詢?nèi)绱笥诘扔诘谌械挠涗?，可以使用差集運(yùn)算minus，先求出總集合，再減去小于第三行的記錄即可：

SQL> select * from dept
  2  minus
  3  select * from dept where rownum<3;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON
  • ROWNUM和Order BY

在使用ROWNUM時，只有當(dāng)Order By的字段是主鍵時，查詢結(jié)果才會先排序再計算ROWNUM。

例如，在dept表中，deptno是主鍵：

SQL>  select dept.*,rownum
  2  from dept
  3  where rownum<3
  4  order by deptno; /*主鍵排序*/

    DEPTNO DNAME                        LOC                            ROWNUM
---------- ---------------------------- -------------------------- ----------
        10 ACCOUNTING                   NEW YORK                            1
        20 RESEARCH                     DALLAS                              2

SQL> select dept.*,rownum
  2  from dept
  3  where rownum<3
  4   order by loc;  /*非主鍵排序*/

    DEPTNO DNAME                        LOC                            ROWNUM
---------- ---------------------------- -------------------------- ----------
        20 RESEARCH                     DALLAS                              2
        10 ACCOUNTING                   NEW YORK                            1

出現(xiàn)這種混亂的原因是：oracle先按物理存儲位置順序（rownum）取出物理位置上的前2條數(shù)據(jù)，然后再對這些數(shù)據(jù)按照Order By的字段進(jìn)行排序，而不是我們所期望的先排序、再取特定記錄數(shù)。
解決方案是：

SQL> select * from(
  2     select * from dept
  3     order by loc
  4  )
  5  where rownum<3;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        40 OPERATIONS                   BOSTON
        30 SALES                        CHICAGO

3.如下三個查詢的結(jié)果是相同的，但是性能是由差到好的：

  • 差 :

/ROWNUM對結(jié)果進(jìn)行分頁，下面返回結(jié)果中的第6到第10條記錄/
select * from(
select contacts*, rownum as rn
from contacts
where phone = '(1) 925-4604800'
) res
where res.rn between 6 and 10;
```

  • 中：

select * from contacts
where phone = '(1) 925-4604800'
and rownum <= 10
MINUS /求集合差/
select * from contacts
where phone = '(1) 925-4604800'
and rownum <= 5
;
```

  • 好：

select * from(
select contacts.*, rownum as rn
from contacts
where phone = '(1) 925-4604800' AND rownum <= 10
) res where res.rn > 5;
```

4.分組標(biāo)號

設(shè)有一個親子關(guān)系的parentage表：

SQL> select * from parentage;

FATHER     CHILD
---------- ----------
David      Cindy
David      Tomy
Tom        Amy
Tom        Bob
Tom        Nick

主碼是全碼。
下面的語句：

SELECT DECODE(a.rn-min_sno,0,a.father,NULL) father,
        DECODE(a.rn-min_sno,0,1,a.rn+1-min_sno) sno, 
        a.child
 FROM   (
            SELECT parentage.*,rownum as rn
            FROM parentage
            ORDER BY father, child 
       ) a,
        (
            SELECT father, MIN(rownum) min_sno
            FROM( 
                    SELECT *
                    FROM parentage
                    ORDER BY father, child
                )
            GROUP BY father
       ) b
  WHERE a.father=b.father;

將會輸出：


FATHER            SNO CHILD
---------- ---------- ----------
Tom                 1 Amy
                    2 Bob
                    3 Nick
David               1 Cindy
                    2 Tomy

特別注意：條件判斷函數(shù)decode語句
decode( A, B1,C1,B2,C2,....,Bn-1,Cn-1,Cn)
等價于如下的邏輯代碼：

if（A==B1):
    return C1;
else if(A==B2):
    return C2;
....
else:
    return Cn;
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容