《Oracle 入門教程》第 07 篇 限制返回行數(shù)


作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

    • 7.1 使用 FETCH 子句實現(xiàn)排行榜
    • 7.2 使用 ROWNUM 實現(xiàn)排行榜
    • 7.3 使用 OFFSET 子句分頁查詢
    • 7.4 使用 ROWNUM 實現(xiàn)分頁查詢

Top-N 排行榜是一個常見的業(yè)務需求，例如銷量排行榜、電影排行榜等。另外，很多時候查詢的結果可能包含成千上萬行數(shù)據(jù)，但是在頁面前端為了便于查看每次只顯示一定數(shù)量的結果，也就是查詢的分頁功能。Oracle 提供了 SQL 標準中的FETCH和OFFSET子句，可以實現(xiàn)以上功能；另外，我們也可以使用 Oracle 中的偽列ROWNUM實現(xiàn)相同的效果。
7.1 使用 FETCH 子句實現(xiàn)排行榜

數(shù)據(jù)排行榜的本質就是先排序，然后再返回前 N 條記錄。例如，以下語句查詢薪水最高的 3 名員工：

SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;
EMP_NAME|SALARY|
--------|------|
劉備     | 30000|
關羽     | 26000|
張飛     | 24000|

 

其中，ORDER BY 按照月薪從高到低進行排序，如果不指定排序的話不保證返回薪水最高的員工；FETCH 表示返回前 3 條數(shù)據(jù)，也就是月薪 Top-3 的員工；FIRST也可以寫成NEXT，ROWS也可以寫成ROW。

除了限制返回多少行外，F(xiàn)ETCH 還可以按照百分比指定返回的數(shù)據(jù)量。完整的 FETCH 語法如下：

SELECT column1, column2, ...
FROM table_name
[WHERE conditions]
[ORDER BY ...]
[OFFSET m {ROW | ROWS}]
FETCH { FIRST | NEXT } [ num_rows | n PERCENT ] { ROW | ROWS } { ONLY | WITH TIES };

 

其中，方括號（[ ]）表示可選項；大括號（{ }）是必選項，豎線（|）表示可以二選一。每個參數(shù)的作用如下：

    OFFSET表示偏移量，即從第 m+1 行開始返回；如果不指定或者指定 0 或負數(shù)，表示從第 1 行開始返回；OFFSET 通?？梢杂糜趯崿F(xiàn)分頁查詢；
    FETCH指定返回多少行，F(xiàn)IRST 和 NEXT 作用相同；
    num_rows 表示按照行數(shù)計算返回的數(shù)據(jù)量，n PERCENT 表示按照百分比計算返回的數(shù)據(jù)量，ROW 和 ROWS 作用相同；
    ONLY和WITH TIES的區(qū)別在于如果最后有多個排名相同的數(shù)據(jù)行，WITH TIES 會返回更多的數(shù)據(jù)；默認為 ONLY。

例如，以下示例使用 WITH TIES 選項返回了更多的數(shù)據(jù)：

SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
FETCH FIRST 3 ROWS WITH TIES;
EMP_NAME|SALARY|
--------|------|
劉備     | 30000|
關羽     | 26000|
張飛     | 24000|
諸葛亮    | 24000|

 

由于“諸葛亮”和“張飛”的月薪相同，WITH TIES 返回了 4 條記錄。

以下示例按照百分比返回月薪最高的前 10% 員工：

SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
FETCH FIRST 10 PERCENT ROWS ONLY;
EMP_NAME|SALARY|
--------|------|
劉備     | 30000|
關羽     | 26000|
張飛     | 24000|

 

員工表中總共有 25 人，百分之十約為 3 人。我們同樣可以使用 WITH TIES 返回更多數(shù)據(jù)。
7.2 使用 ROWNUM 實現(xiàn)排行榜

Oracle 11g 或者更早的版本中無法使用 FETCH 語句， 不過可以利用子查詢和 ROWNUM 函數(shù)實現(xiàn)相同的功能。例如：

SELECT emp_name, salary, rownum AS rn
FROM (SELECT emp_name, salary
      FROM employee
      ORDER BY salary DESC
     ) emp_order
WHERE rownum <= 3;
EMP_NAME|SALARY|RN|
--------|------|--|
劉備     | 30000| 1|
關羽     | 26000| 2|
張飛     | 24000| 3|

 

其中，包含 ORDER BY 子句的 SELECT 語句是一個內部子查詢，emp_order 是它的別名，它的作用是將員工按照月薪從高到低進行排序；外部的 SELECT 語句使用 ROWNUM 函數(shù)為排序后的數(shù)據(jù)指定一個數(shù)字編號，并且在查詢條件中前 3 條記錄，也就是水最高的前 3 名員工。

    ??我們將會在第 16 篇中介紹 Oracle 中的子查詢。

子查詢加 ROWNUM 函數(shù)的方式不支持按照百分比限制行數(shù)和 WITH TIES 選項。
7.3 使用 OFFSET 子句分頁查詢

前端頁面通常都需要支持分頁顯示功能，也就是每頁顯示一定數(shù)量的記錄（例如 10 行），同時提供“上一頁”和“下一頁”等導航按鈕。這種分頁查詢的本質就是先跳過指定的行數(shù)，再返回 Top-N 記錄，示意圖如下：


實際上，Top-N 排行榜是分頁查詢的一個特殊情況。使用 SQL 實現(xiàn)這種功能需要引入另一個子句：OFFSET。

假設我們的應用提供了分頁顯示，每頁顯示 10 條記錄?，F(xiàn)在用戶點擊了下一頁，需要顯示第 11 到第 20 條記錄。使用標準 SQL 語法實現(xiàn)如下：

SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH FIRST 10 ROWS ONLY;
EMP_NAME|SALARY|
--------|------|
關興     |  7000|
關平     |  6800|
趙氏     |  6600|
廖化     |  6500|
張苞     |  6500|
孫丫鬟    |  6000|
趙統(tǒng)     |  6000|
馬岱     |  5800|
簡雍     |  4800|
孫乾     |  4700|

 

其中，ORDER BY 按照月薪從高到低進行排序；OFFSET 跳過 10 條數(shù)據(jù)；然后 FETCH 返回隨后的 10 條數(shù)據(jù)。對于應用程序而言，先通過 COUNT() 函數(shù)獲取數(shù)據(jù)的總行數(shù)，然后傳入不同的OFFSET偏移量和FETCH數(shù)量，就可以在查詢結果中任意導航。

    ??隨著OFFSET的增加，查詢的性能會越來越差。因為服務器需要計算更多的偏移量，即使這些數(shù)據(jù)不需要被返回前端。

OFFSET 還可以用于實現(xiàn)其他功能。例如，以下語句可以找出月薪排名第 3 高的員工：

SELECT emp_name, salary
FROM employee
ORDER BY salary DESC
OFFSET 2 ROWS
FETCH FIRST 1 ROWS WITH TIES;
EMP_NAME|SALARY|
--------|------|
張飛     | 24000|
諸葛亮    | 24000|

 

7.4 使用 ROWNUM 實現(xiàn)分頁查詢

Oracle 11g 或者更早的版本也可以使用子查詢和 ROWNUM 函數(shù)實現(xiàn)分頁查詢功能。例如：

SELECT emp_name, salary
FROM (SELECT emp_name, salary, rownum AS rn
      FROM (SELECT emp_name, salary
            FROM employee
            ORDER BY salary DESC
           ) emp_order
      WHERE rownum <= 20
     ) emp_rownum
WHERE rn >= 11;
EMP_NAME|SALARY|
--------|------|
關興     |  7000|
關平     |  6800|
趙氏     |  6600|
廖化     |  6500|
張苞     |  6500|
趙統(tǒng)     |  6000|
孫丫鬟    |  6000|
馬岱     |  5800|
簡雍     |  4800|
孫乾     |  4700|

 

其中，最里層包含 ORDER BY 的子查詢（emp_order）將員工按照月薪從高到低進行排序；中間的 SELECT 語句（emp_rownum）使用 ROWNUM 函數(shù)為排序后的數(shù)據(jù)指定一個數(shù)字編號，并且限制編號小于等于 20；最外層的查詢返回編號大于等于 11 的數(shù)據(jù)。

注意，我們必須使用 3 個查詢語句，而不能寫成以下形式：

SELECT emp_name, salary, rownum AS rn
FROM (SELECT emp_name, salary
      FROM employee
      ORDER BY salary DESC
     ) emp_order
WHERE rownum >= 11 AND rownum <= 20;
EMP_NAME|SALARY|RN|
--------|------|--|

 

因為 ROWNUM 生成的數(shù)字從 1 開始，以上查詢條件中的 rownum >= 11 不會成立，因此不會返回任何數(shù)據(jù)，需要再加上一層嵌套。