oracle分頁sql

A. Oracle常見sql分頁實現方案

在Oracle中 用SQL來實現分頁有很多種實現方式 但有些語句可能並不是很通用 只能用在一些特殊場景之中

以下介紹三種比較通用的實現方案 在以下各種實現中 ROWNUM是一個最核心的關鍵詞 在查詢時他是一個虛擬的列 取值為 到記錄總數的序號

首先來介紹我們工作中最常使用的一種實現方式

SELECT *

FROM (SELECT ROW_ * ROWNUM ROWNUM_

FROM (SELECT *

FROM TABLE

WHERE TABLE _ID = XX

ORDER BY GMT_CREATE DESC) ROW_

WHERE ROWNUM <= )

WHERE ROWNUM_ >= ;

其中最內層的查詢SELECT為不進行翻頁的原始查詢語句 可以用自己的任意Select SQL替換 ROWNUM <= 和ROWNUM >= 控制分頁查詢的每頁的范圍

分頁的目的就是控制輸出結果集大小 將結果盡快的返回 上面的SQL語句在大多數情況擁有較高的效率 主要體現在WHERE ROWNUM <= 這句上 這樣就控制了查詢過程中的最大記錄數

上面例子中展示的在查詢的第二層通過ROWNUM <= 來控制最大值 在查詢的最外層控制最小值 而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 語句 在查詢的最外層控制分頁的最小值和最大值 此時SQL語句如下 也就是要介紹的第二種實現方式

SELECT *

FROM (SELECT A * ROWNUM RN

FROM (SELECT *

FROM TABLE

WHERE TABLE _ID = XX

ORDER BY GMT_CREATE DESC) A)

WHERE RN BEEEN AND ;

由於Oracle可以將外層的查詢條件推到內層查詢中 以提高內層查詢的執行效率 但不能跨越多層

對於第一個查詢語句 第二層的查詢條件WHERE ROWNUM <= 就可以被Oracle推入到內層查詢中 這樣Oracle查詢的結果一旦超過了ROWNUM限制條件 就終止查詢將結果返回了

而 第二個查詢語句 由於查詢條件BEEEN AND 是存在於查詢的第三層 而Oracle無法將第三層的查詢條件推到最內層（即使推到最內層也沒有意義 因為最內層查詢不知道RN代表什麼） 因此 對於第二個查詢語句 Oracle最內層返回給中間層的是所有滿足條件的數據 而中間層返回給最外層的也是所有數據 數據的過濾在最外層完成 顯然這個效率要比第一個查詢低得多

以上兩種方案完全是通過ROWNUM來完成 下面一種則採用ROWID和ROWNUM相結合的方式 SQL語句如下

SELECT *

FROM (SELECT RID

FROM (SELECT R RID ROWNUM LINENUM

FROM (SELECT ROWID RID

FROM TABLE

WHERE TABLE _ID = XX

ORDER BY GMT_CREATE DESC) R

WHERE ROWNUM <= )

WHERE LINENUM >= ) T

TABLE T

WHERE T RID = T ROWID;

從語句上看 共有 層Select嵌套查詢 最內層為可替換的不分頁原始SQL語句 但是他查詢的欄位只有ROWID 而沒有任何待查詢的實際表欄位 具體查詢實際欄位值是在最外層實現的

這種方式的原理大致為 首先通過ROWNUM查詢到分頁之後的 條實際返回記錄的ROWID 最後通過ROWID將最終返回欄位值查詢出來並返回

和前面兩種實現方式相比 該SQL的實現方式更加繁瑣 通用性也不是非常好 因為要將原始的查詢語句分成兩部分（查詢欄位在最外層 表及其查詢條件在最內層）

但這種實現在特定場景下還是有優勢的 比如我們經常要翻頁到很後面 比如 條記錄中我們經常需要查 及其以後的數據 此時該方案效率可能要比前面的高

因為前面的方案中是通過ROWNUM <= 來控制的 這樣就需要查詢出 條數據 然後取最後 之間的數據 而這個方案直接通過ROWID取需要的那 條數據

從不斷向後翻頁這個角度來看 第一種實現方案的成本會越來越高 基本上是線性增長 而第三種方案的成本則不會像前者那樣快速 他的增長只體現在通過查詢條件讀取ROWID的部分

當然 除了以上提了這些方案 我們還可以用以下的SQL來實現

SELECT *

FROM TABLE

WHERE TABLE _ID NOT IN

(SELECT TABLE _ID FROM TABLE WHERE ROWNUM <= )

AND ROWNUM <= ;

SELECT *

FROM TABLE

WHERE ROWNUM <=

MINUS

SELECT * FROM TABLE WHERE ROWNUM <= ;

………………

注意 當ROWNUM作為查詢條件時 他是在order by之前執行 所以要特別小心

比如我們想查詢TABLE 中按TABLE _ID倒序排列的前 條記錄不能用如下的SQL來完成

B. 在oracle資料庫中的分頁SQL語句怎麼寫

前提：

分頁參數：size = 20 page = 2；

沒有order by的查詢；

嵌套子查詢，兩次篩選（推薦使用）。

SQL語句：

SELECT *

FROM (SELECT ROWNUM AS rowno, t.*

FROM DONORINFO t

WHERE t.BIRTHDAY BETWEEN TO_DATE ('19800101', 'yyyymmdd')

AND TO_DATE ('20060731', 'yyyymmdd')

AND ROWNUM <= 20*2) table_alias

WHERE table_alias.rowno > 20*(2-1);

(2)oracle分頁sql擴展閱讀：

rownum總是從1開始的，第一條不滿足去掉的話，第二條的rownum 又成了1。依此類推，所以永遠沒有不滿足條件的記錄。

可以這樣理解：rownum是一個序列，是Oracle資料庫從數據文件或緩沖區中讀取數據的順序。它取得第一條記錄則rownum值為1，第二條為2。

依次類推：當使用「>、>=、=、between...and」這些條件時，從緩沖區或數據文件中得到的第一條記錄的rownum為1，不符合sql語句的條件，會被刪除，接著取下條。下條的rownum還會是1，又被刪除，依次類推，便沒有了數據。

C. oracle rownum分頁

在Oracle資料庫中，rownum是一個預處理欄位，它默認從1開始標注記錄順序。只有當記錄集已經滿足指定條件後，才會進行後續的編號。當使用rownum進行分頁時，如果條件是rownum >= 6，那麼第一條記錄的rownum為1，顯然不滿足條件。Oracle會將第二條記錄的rownum標注為1，繼續進行比較，但同樣不滿足條件。這個過程會不斷循環，直到找到滿足條件的記錄。

為了正確地使用rownum進行分頁，可以採用子查詢的方式。例如，要獲取從第6條開始的前10條記錄，可以使用以下SQL語句：

select * from ( select a1.*, rownum rwn from emp a1 where rownum = 6;

或者使用row_number()函數進行排序：

select * from ( select qx.*,row_number() over(order by qx.empno) rwn from emp qx ) where rwn between 6 and 10;

這兩種方法都能確保從第6條記錄開始，獲取符合條件的前10條記錄。

使用row_number()函數的好處在於它可以根據指定的排序規則對記錄進行編號，而rownum的編號是基於查詢條件的匹配結果進行的，可能會導致分頁結果不符合預期。因此，在進行分頁操作時，推薦使用row_number()函數。

需要注意的是，上述SQL語句中的emp表是一個示例表名，實際使用時需要替換為具體的表名。同時，row_number()函數的排序規則可以根據實際需求進行調整。

綜上所述，正確使用rownum和row_number()函數，可以有效地進行Oracle資料庫中的分頁操作，確保分頁結果的准確性。