Oracle Top N 和 Oracle中的limit問題解決方案

oracle top N

1.在ORACLE中實現SELECT TOP N

   由於ORACLE不支援SELECT TOP語句，所以在ORACLE中經常是用ORDER BY跟ROWNUM的組合來實現SELECT TOP N的查詢。

簡單地說，實現方法如下所示：

      SELECT　列名１．．．列名ｎ　FROM

        (SELECT　列名１．．．列名ｎ　FROM 表名 ORDER BY 列名１．．．列名ｎ)

       WHERE ROWNUM <= N（抽出記錄數）

      ORDER BY ROWNUM ASC

   下面舉個例子簡單說明一下。

顧客表customer(id,name)有如下資料：

      ID NAME

       01 first

       02 Second

       03 third

       04 forth

       05 fifth

       06 sixth

       07 seventh

       08 eighth

       09 ninth

       10 tenth

       11 last

   則按NAME的字母順抽出前三個顧客的SQL語句如下所示：

      SELECT * FROM

        (SELECT * FROM CUSTOMER ORDER BY NAME)

       WHERE ROWNUM <= 3

       ORDER BY ROWNUM ASC

   輸出結果為：

      ID NAME

       08 eighth

       05 fifth

       01 first

2.在TOP N紀錄中抽出第M（M <= N）條記錄

在得到了TOP
N的資料之後，為了抽出這N條記錄中的第M條記錄，我們可以考慮從ROWNUM著手。我們知道，ROWNUM是記錄表中資料編號的一個隱藏子段，所以可以
在得到TOP N條記錄的時候同時抽出記錄的ROWNUM，然後再從這N條記錄中抽取記錄編號為M的記錄，即使我們希望得到的結果。

從上面的分析可以很容易得到下面的SQL語句。

      SELECT 列名１．．．列名ｎ FROM

         (

         SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM

           (SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)

         WHERE ROWNUM <= N（抽出記錄數）

       ORDER BY ROWNUM ASC

         )

       WHERE RECNO = M（M <= N）

同樣以上表的資料為基礎，那麼得到以NAME的字母順排序的第二個顧客的資訊的SQL語句應該這樣寫：

       SELECT ID, NAME FROM

         (

          SELECT ROWNUM RECNO, ID, NAME FROM

            (SELECT * FROM CUSTOMER ORDER BY NAME)

             WHERE ROWNUM <= 3

             ORDER BY ROWNUM ASC )

           WHERE RECNO = 2

     結果則為：

       ID NAME

        05 fifth

3.抽出按某種方式排序的記錄集中的第N條記錄

   在2的說明中，當M = N的時候，即為我們的標題講的結果。實際上，2的做法在裡面N>M的部分的資料是基本上不會用到的，我們僅僅是為了說明方便而採用。

   如上所述，則SQL語句應為：

       SELECT 列名１．．．列名ｎ FROM

         (

          SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM

            (SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)

             WHERE ROWNUM <= N（抽出記錄數）

          ORDER BY ROWNUM ASC

         )

         WHERE RECNO = N

     那麼，2中的例子的SQL語句則為：

        SELECT ID, NAME FROM

          (

           SELECT ROWNUM RECNO, ID, NAME FROM

             (SELECT * FROM CUSTOMER ORDER BY NAME)

           WHERE ROWNUM <= 2

           ORDER BY ROWNUM ASC

          )

          WHERE RECNO = 2

     結果為：

       ID NAME

        05 fifth

4.抽出按某種方式排序的記錄集中的第M條記錄開始的X條記錄

      
3裡所講得僅僅是抽取一條記錄的情況，當我們需要抽取多條記錄的時候，此時在2中的N的取值應該是在N >= (M + X -
1)這個範圍內，當讓最經濟的取值就是取等好的時候了的時候了。當然最後的抽取條件也不是RECNO = N了，應該是RECNO BETWEEN M
AND (M + X - 1)了，所以隨之而來的SQL語句則為：

       SELECT 列名１．．．列名ｎ FROM

        (

         SELECT ROWNUM RECNO, 列名１．．．列名ｎFROM

          (

          SELECT 列名１．．．列名ｎ FROM 表名 ORDER BY 列名１．．．列名ｎ)

          WHERE ROWNUM <= N （N >= (M + X - 1)）

        ORDER BY ROWNUM ASC

          )

         WHERE RECNO BETWEEN M AND (M + X - 1)

    同樣以上面的資料為例，則抽取NAME的字母順的第2條記錄開始的3條記錄的SQL語句為：

       SELECT ID, NAME FROM

         (

          SELECT ROWNUM RECNO, ID, NAME FROM

            (SELECT * FROM CUSTOMER ORDER BY NAME)

          WHERE ROWNUM <= (2 + 3 - 1)

          ORDER BY ROWNUM ASC

         )

         WHERE RECNO BETWEEN 2 AND (2 + 3 - 1)

     結果如下：

       ID NAME

        05 fifth

        01 first

        04 forth

    以此為基礎，再擴充的話，做成預存程序，將開始記錄數以及抽取記錄數為參數，就可以輕鬆實現分頁抽取資料。

Oracle中的limit問題解決方案2009-05-12 17:19

 

從mysql到oracle遷移一個程式，遇到了sql語句中的limit問題。
查遍網路，所提供的方法都極其麻煩，不利於通用。
以下是我的解決方案，可以與limit媲美。

比如從一個mobileuser 使用者表中查詢2到6條記錄，按照第一次使用時間排序。

mysql語句為：

SELECT userid,password,firstusetime from mobileuser ORDER BY firstusetime DESC limit 2,6;

oracle語句為：

SELECT
* FROM ( SELECT userid,password,firstusetime, RANK() OVER (ORDER BY
firstusetime DESC ) RN   FROM mobileuser ) WHERE RN between 2 and 6;

rank() over 函數代表排行依據，整個sql語句就是從根據“ORDER BY firstusetime DESC”這個標準，找到排行2到6位的資料。

但是存在一個問題，根據排行依據，有些資料是並列的，這樣返回的資料條數就會多於我們期望的。這時我們加一個rownum限制就行了。

SELECT
* FROM ( SELECT userid,password,firstusetime, RANK() OVER (ORDER BY
firstusetime DESC ) RN   FROM mobileuser ) WHERE RN between 2 and 6 and
rownum<=5;