oracle中rank与rownum,Oracle中rownum、rowid、row_number()、rank()、dense_rank()的區別

首先來說一下rownum與rowid含義：

顧名思義rownum就是行數/行號，而rowid就是編碼/編號/唯一識別號，所以他是類似“AAAR8gAAEAAAAErAAK”的編號，注意他是沒有先后順序的，也就是說他和數據入庫時間沒有任何關系，打個比方：他就像磁盤、內存存儲數據用的是16進制的地址一樣。

他們都是偽列，可以理解成表中的一個列只是他們並不是你創建的。同樣是偽列區別是什么呢？

rowid是你錄入數據時有數據庫自動為這條記錄添加的唯一的18位編號是一個物理編號用於找到這條記錄(順便說一句這也是為什么數據優調的時候強調盡量使用rowid的原因)，他是不會隨着查詢而改變的 除非在表發生移動(比如表空間變化，數據導入/導出以后)，才會發生變化。

rownum是根據sql查詢后得到的結果自動加上去的，但是他卻不受到sql中order by排序的影響，因為他和rowid的順序一樣是系統按照記錄插入時的順序給記錄排的號(順序的、無跳躍)。 但是如果你想讓rownum和order by一樣的順序 那么可以使用子查詢，形如：select rownum,t.* from (select * from 表空間名 order by 字段名) t  這樣的話rownum就是根據該字段進行排序的編號了，為什么會這樣呢，本人理解：rownum是根據表記錄輸出的行號，與篩選語句、排序語句都無關所以當用子查詢時等於生成了一個表於是就按照這張表從1開始排序了。 同樣，也可以用下面要提得到的分析函數中的row_number() over(order by 需要排序的字段名)。

值得一提的是MSSQL是沒有rownum和rowid的。

下面說說分析函數row_number()、rank()、dense_rank()

ROW_NUMBER()：

Row_number函數返回一個唯一的值，當碰到相同數據時，排名按照記錄集中記錄的順序依次遞增。 row_number()和rownum差不多，功能更強一點(可以在各個分組內從1開時排序)，因為row_number()是分析函數而rownum是偽列所以row_number()一定要over而rownum不能over。

RANK()：

Rank函數返回一個唯一的值，除非遇到相同的數據，此時所有相同數據的排名是一樣的，同時會在最后一條相同記錄和下一條不同記錄的排名之間空出排名。rank()是跳躍排序，有兩個第二名時接下來就是第四名(同樣是在各個分組內)。

DENSE_RANK()：

Dense_rank函數返回一個唯一的值，除非當碰到相同數據，此時所有相同數據的排名都是一樣的。

dense_rank()是連續排序，有兩個第二名時仍然跟着第三名。他和row_number的區別在於row_number是沒有重復值的。

下面舉個例子：

【1】測試環境：SQL> desc user_order;

Name                                      Null?    Type

----------------------------------------- -------- ---------------------------- REGION_ID                                          NUMBER(2)

CUSTOMER_ID                                     NUMBER(2)

CUSTOMER_SALES                               NUMBER

【2】測試數據：SQL> select * from user_order order by customer_sales;

REGION_ID CUSTOMER_ID CUSTOMER_SALES

---------- ----------- --------------         5           1              151162

10          29             903383

6           7              971585

10          28            986964

9          21           1020541

9          22           1036146

8          16           1068467

6           8            1141638

5           3            1161286

5           5            1169926

8          19           1174421

7          12           1182275

7          11           1190421

6          10           1196748

6           9            1208959

10          30          1216858

5             2                1224992

9             24              1224992

9             23              12249928          18           1253840

7          15           1255591

7          13           1310434

10          27          1322747

8          20           1413722

6           6            1788836

10          26          1808949

5           4            1878275

7          14           1929774

8          17           1944281

9          25           2232703

30 rows selected.

【3】row_number()、rank()、dense_rank()這三個分析函數的區別實例

SQL> select region_id, customer_id, sum(customer_sales) total,

2         rank() over(orderbysum(customer_sales)desc) rank,

3         dense_rank() over(orderbysum(customer_sales)desc) dense_rank,

4         row_number() over(orderbysum(customer_sales)desc) row_number

5    from user_order

6   group by region_id, customer_id;

REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER

---------- ----------- ---------- ---------- ---------- ----------

 

        

8          18                1253840         11         11         11

5           2                 1224992         12         12         12

9          23                1224992         12         12         13

9          24                1224992         12         12         14

10          30               1216858         151315

 

30 rows selected.

請注意上面的綠色高亮部分，這里生動的演示了3種不同的排名策略：

①對於第一條相同的記錄，3種函數的排名都是一樣的：12

②當出現第二條相同的記錄時，Rank和Dense_rank依然給出同樣的排名12；而row_number則順延遞增為13，依次類推至第三條相同的記錄

③當排名進行到下一條不同的記錄時，可以看到Rank函數在12和15之間空出了13,14的排名，因為這2個排名實際上已經被第二、三條相同的記錄占了。而Dense_rank則順序遞增。row_number函數也是順序遞增

比較上面3種不同的策略，我們在選擇的時候就要根據客戶的需求來定奪了：

①假如客戶就只需要指定數目的記錄，那么采用row_number是最簡單的，但有漏掉的記錄的危險

②假如客戶需要所有達到排名水平的記錄，那么采用rank或dense_rank是不錯的選擇。至於選擇哪一種則看客戶的需要，選擇dense_rank或得到最大的記錄