Oracle中rownum和row_number()

風靈使發表於2018-04-18
Oracle

row_number()over(partition by col1 order by col2)表示根據col1分組，在分組內部根據col2排序，而此函式計算的值就表示每組內部排序後的順序編號（組內連續的唯一的）。 與rownum的區別在於：使用rownum進行排序的時候是先對結果集加入偽劣rownum然後再進行排序，而row_number()在包含排序從句後是先排序再計算行號碼。

一、oracle中rownum

用於從查詢返回的行的編號，返回的第一行分配的是1，第二行是2，依此類推，這個偽欄位可以用於限制查詢返回的總行數，而且rownum不能以任何表的名稱作為字首。

1、rownum 對於等於某值的查詢條件

如果希望找到學生表中第一條學生的資訊，可以使用rownum=1作為條件。但是想找到學生表中第二條學生的資訊，使用rownum=2結果查不到資料。因為rownum都是從1開始，但是1以上的自然數在rownum做等於判斷是時認為都是false條件，所以無法查到rownum = n（n>1的自然數）。

SQL> select rownum,id,name from student where rownum=1; --有記錄
SQL> select rownum,id,name from student where rownum =2; --無記錄

2、rownum對於大於某值的查詢條件

如果想找到從第二行記錄以後的記錄，當使用rownum>2是查不出記錄的，可以使用以下的子查詢方法來解決。注意子查詢中的rownum必須要有別名，否則還是不會查出記錄來，這是因為rownum不是某個表的列，如果不起別名的話，無法知道rownum是子查詢的列還是主查詢的列。

SQL>select * from(select rownum no ,id,name from student) where no>2; --有記錄
SQL> select * from(select rownum,id,name from student)where rownum>2; --無記錄

3、rownum對於小於某值的查詢條件

如果想找到第三條記錄以前的記錄，當使用rownum<3是能得到兩條記錄的。顯然rownum對於rownum<n（(n>1的自然數）的條件認為是成立的，所以可以找到記錄。

SQL> select rownum,id,name from student where rownum <3; --有記錄

4、rownum和排序

Oracle中的rownum的是在取資料的時候產生的序號，所以想對指定排序的資料去指定的rowmun行資料就必須注意了。

SQL> select rownum ,id,name from student order by name;
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
3 200003 李三
2 200002 王二
1 200001 張一
4 200004 趙四

可以看出，rownum並不是按照name列來生成的序號。系統是按照記錄插入時的順序給記錄排的號，rowid也是順序分配的。為了解決這個問題，必須使用子查詢

SQL> select rownum ,id,name from (select * from student order by name);
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200003 李三
2 200002 王二
3 200001 張一
4 200004 趙四

二、oracle中row_number()

1、row_number() over (order by col_1[,col_2 ...])
按照col_1[,col_2 ...]排序,返回排序後的結果集，並且為每一行返回一個不相同的值。

2、row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
先按照col_n[,col_m ...進行分組，再在每個分組中按照col_1[,col_2 ...]進行排序(升序)，最後返回排好序後的結果集

oracle中row_number()例項

1.使用row_number（）函式進行編號，如

select email,customerID, ROW_NUMBER() over(order by psd) as rows from QT_Customer

原理：先按psd進行排序，排序完後，給每條資料進行編號。

2.在訂單中按價格的升序進行排序，並給每條記錄進行排序程式碼如下：

select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

3、統計每一個客戶最近下的訂單是第幾次下的訂單。

with tabs as 
( 
select ROW_NUMBER() over(partition by customerID order by totalPrice) as rows,customerID,totalPrice, DID from OP_Order 
) 
select MAX(rows) as '下單次數',customerID from tabs group by customerID

4、在使用over等開窗函式時，over裡頭的分組及排序的執行晚於“where，group by，order by”的執行。

select ROW_NUMBER() over(partition by customerID order by insDT) as rows, customerID,totalPrice, DID from OP_Order where insDT>'2011-07-22'

以上程式碼是先執行where子句，執行完後，再給每一條記錄進行編號。

三、row_number()與rownum的區別

使用rownum進行排序的時候是先對結果集加入偽劣rownum然後再進行排序，而row_number()在包含排序從句後是先排序再計算行號碼。

If opportunity doesn’t knock, build a door

rownum,rowid,row_number()及oracle分頁查詢

1.rownum和rowid都是偽列，但兩者的根本是不同的，rownum是根據sql查詢出來的結果給每行分配一個邏輯編號，不同的sql也就會導致rownum不同，但是rowid是物理結構上的，在每條記錄Insert到資料庫時，就會有一個唯一的物理記錄。rowid可以說是物理存在的，表示記錄在表空間中的一個唯一位置ID，在DB中唯一。只要記錄沒被搬動過，rowid是不變的。rowid相對於表來說相當於一般列。

rownum常用語分頁查詢，rowid則常用語去除重複記錄。

2.rownum是對結果集加的一個偽列，即先查到結果集之後再加上去的一個列(先有結果集)。簡單的說，rownum是對符合條件結果的序列號。總是從1開始排起，所以選出的結果中不能沒有1而有其他大於1的值。

也可以理解為rownum是oracle資料庫從資料檔案或緩衝區中讀取資料的順序。它取得第一條記錄則rownum為1，

第2條為2。如果使用>，>=,=,between...and這些條件，因為從緩衝區或資料檔案中得到的第一條記錄的rownum

為1，則被刪除，接著取下條，可是rownum依舊為1，有被刪除，便沒有了資料。

case1:

    SQL> select * from emp where rownum between 2 and 5;  
    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO  
    ----- ---------- --------- ----- ----------- --------- --------- ------

case2:

    SQL> select * from emp where rownum<>3;  
    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO  
    ----- ---------- --------- ----- ----------- --------- --------- ------  
     7369 SMITH      CLERK      7902 1980/12/17     800.00               20  
     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

case3:

    SQL> select * from emp where rownum>0;  
    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO  
    ----- ---------- --------- ----- ----------- --------- --------- ------  
     7369 SMITH      CLERK      7902 1980/12/17     800.00               20  
     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30  
     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30  
     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20  
     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30  
     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30  
     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10  
     7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20  
     7839 KING       PRESIDENT       1981/11/17    5000.00               10  
     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30  
     7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20  
     7900 JAMES      CLERK      7698 1981/12/3      950.00               30  
     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20  
     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10  
    14 rows selected

case4:

    SQL> select * from emp where rownum between 1 and 3;  
    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO  
    ----- ---------- --------- ----- ----------- --------- --------- ------  
     7369 SMITH      CLERK      7902 1980/12/17     800.00               20  
     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30  
     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

3.rownum和row_numbe() over:

使用rownum進行排序的時候是先對結果集加入偽列rownum然後再進行排序，而row_number()在包含排序從句後是先排序再計算行號碼

case:

order by:

    SQL> select a.*,rownum from emp a order by a.ename;  
    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO     ROWNUM  
    ----- ---------- --------- ----- ----------- --------- --------- ------ ----------  
     7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20         11  
     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30          2  
     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30          6  
     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10          7  
     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20         13  
     7900 JAMES      CLERK      7698 1981/12/3      950.00               30         12  
     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20          4  
     7839 KING       PRESIDENT       1981/11/17    5000.00               10          9  
     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30          5  
     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10         14  
     7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20          8  
     7369 SMITH      CLERK      7902 1980/12/17     800.00               20          1  
     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30         10  
     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30          3  
    14 rows selected

row_number() over(order by )

    SQL> select a.*,row_number() over(order by a.ename) from emp a;  
    EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO ROW_NUMBER()OVER(ORDERBYA.ENAM  
    ----- ---------- --------- ----- ----------- --------- --------- ------ ------------------------------  
     7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20                              1  
     7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30                              2  
     7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30                              3  
     7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10                              4  
     7902 FORD       ANALYST    7566 1981/12/3     3000.00               20                              5  
     7900 JAMES      CLERK      7698 1981/12/3      950.00               30                              6  
     7566 JONES      MANAGER    7839 1981/4/2      2975.00               20                              7  
     7839 KING       PRESIDENT       1981/11/17    5000.00               10                              8  
     7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30                              9  
     7934 MILLER     CLERK      7782 1982/1/23     1300.00               10                             10  
     7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20                             11  
     7369 SMITH      CLERK      7902 1980/12/17     800.00               20                             12  
     7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30                             13  
     7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30                             14  
    14 rows selected

4.

case1：在最外層控制分頁的最小值和最大值

    SELECT *  
      FROM (SELECT A.*, ROWNUM RN  
              FROM GAS_CA.A_CARD_METER_PAY A  
             WHERE A.PURCH_GAS_TIME BETWEEN TO_DATE('2015/1/1', 'YYYY/MM/DD') AND  
                   TO_DATE('2016/1/1', 'YYYY/MM/DD')  
               AND A.ORG_NO LIKE '1009%')  
     WHERE RN BETWEEN 10 AND 20;

case2：

    SELECT *  
      FROM (SELECT ROWNUM RN, A.*  
              FROM GAS_CA.A_CARD_METER_PAY A  
             WHERE A.PURCH_GAS_TIME BETWEEN TO_DATE('2015/1/1', 'YYYY/MM/DD') AND  
                   TO_DATE('2016/1/1', 'YYYY/MM/DD')  
               AND A.ORG_NO LIKE '1009%'  
               AND ROWNUM <= 20)  
     WHERE RN >= 10;

對於case1和case2，絕大多數情況下，case2要比第一個查詢效率高得多。

因為在CBO優化模式下，oracle可以將外層的查詢條件推到內層查詢中，以提高內層查詢的執行效率。對於第二份語句，第二層的查詢條件where rownum<=40就可以被oracle推入到內層查詢中，這樣oracle查詢的結果一旦查過rownum限制的條件，就終止查詢將結果返回。而第一個語句，oracle無法將第三層的條件推到最內層(即使推到最內層也沒有意義，因為不知道RN代表什麼)。

case3:有order by 的寫法(較高效率)

    SELECT T1.*, RN  
      FROM (SELECT ROWNUM RN, T.*  
              FROM (SELECT *  
                      FROM GAS_CA.A_CARD_METER_PAY A  
                     WHERE A.PURCH_GAS_TIME BETWEEN  
                           TO_DATE('2015/1/1', 'YYYY/MM/DD') AND  
                           TO_DATE('2016/1/1', 'YYYY/MM/DD')  
                       AND A.ORG_NO LIKE '1009%'  
                     ORDER BY A.PURCH_GAS_TIME) T  
             WHERE ROWNUM <= 20) T1  
     WHERE RN >= 10;

case4:將查詢條件放置最外層

    SELECT *  
      FROM (SELECT T.*, ROWNUM RN  
              FROM (SELECT *  
                      FROM GAS_CA.A_CARD_METER_PAY A  
                     WHERE A.PURCH_GAS_TIME BETWEEN  
                           TO_DATE('2015/1/1', 'YYYY/MM/DD') AND  
                           TO_DATE('2016/1/1', 'YYYY/MM/DD')  
                       AND A.ORG_NO LIKE '1009%'  
                     ORDER BY A.PURCH_GAS_TIME) T) T1  
     WHERE T1.RN BETWEEN 10 AND 20;

ORACLE中的rownum排序及row_number()排序的區別

ORACLE中常用的是通過rownum來分頁，下面來討論下rownum分頁的原理，rownum分頁排序和row_number()函式分頁排序的區別。

菜鳥第一次用rownum容易犯的錯：

select * from emp where rownum>=5 and rownum<=10

執行後返回的結果集是空的。因為rownum是oracle預處理欄位，預設標序是1，只有記錄集已經滿足條件後才會進行後續編號。這樣你查詢emp表時遍歷第一條資料時rownum是1，不符合條件，繼續遍歷到第二條資料rownum仍為1，仍不符合條件，直至遍歷完所有資料，都無資料返回。所以正確的寫法是：

select * from (select rownum r,emp.* from emp where rownum<=10)e where e.r>=5

來簡單解釋下，首先此語句中select rownum r,emp.* from emp where rownum<=10被執行後當做一個檢視(這裡說下什麼時候查詢是一個檢視什麼時候叫子查詢，在from後面的巢狀查詢是檢視，在where後的巢狀查詢是子查詢)，這個檢視中的資料：包括emp表中rownum從1到10的資料，和一個真實列r列（可理解為此時rownum偽列已插入到檢視中被當做真實列），然後e.r表示查詢這個檢視中列r數字大於5的記錄。若你不寫e.r而是寫rownum>=5依舊沒有資料返回，是因為此時再寫rownum是查詢檢視時重新生成偽列rownum了，而未用那個真實列r。就會犯上面的第一個錯誤。

下面繼續說通過rownum分頁和row_number()函式的區別。

我們如果想查得通過empno排序後的分頁資料，看幾條sql：

sql1：

select * from (select rownum rr,emp.* from emp  where rownum<=10 order by empno)v where v.rr>=5

sql2：

select * from (select rownum rr,e.* from (select * from emp order by empno)e  where rownum<=10)v where v.rr>=5

sql3:

select * from (select row_number() over(order by empno) rr,emp.ename from emp)v where rr between 5 and 10

執行後發現都能返回結果集，但是sql1返回的結果集卻不是我們想要的。

將sql1中的子查詢提出來看： 

select rownum rr,emp.* from emp  where rownum<=10 order by empno

這條語句parse的順序是先執行from語句，再執行where再執行order by。where rownum<=10執行完後會隨機取出10條資料，這10條資料的rownum是1到10，再對這10條資料根據進行排序。這樣就不是我們想得到的。

sql2，sql3執行後返回的結果都是我們想要的。sql2是現在子查詢中排完序再進行rownum的篩選。

總結下,rownum排序和row_number()的區別：使用rownum進行排序的時候是先對結果集加入偽列rownum後再進行排序（如sql1的結果），而row_number()在通過over（）裡的排序過後再對每列加上一個數字，從以上三條sql可看出，rownum排序用到了起碼三層巢狀執行效率上不如row_number().

相關文章