oracle sql 分頁

標籤：ar   使用   for   sp   strong   資料   on   art   bs   

Oracle實現分頁時，需要引入一個rownum的函數，rownum可以給記錄一個類似於id的欄位。

以下收整理了常用的幾種sql分頁演算法，資料庫以Oracle中emp為例。查詢結果如下：

SQL> select * from emp;

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
分頁方法1：使用巢狀查詢

SQL> select * from ( select t1.* , rownum as n from emp t1 where rownum  <= 15 ) where n >= 10;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO          N

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

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30         10

 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20         11

 7900 JAMES      CLERK      7698 1981/12/3      950.00               30         12

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20         13

 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10         14

 

分頁方法2：使用minus

SQL> select * from emp where rownum <= 14 minus select * from emp where rownum < 10;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 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

在方法二中，如果minus後的查詢語句執行where rownum > 10時，為什麼不能查詢到任何的記錄呢？

ROWNUM是對結果集加的一個偽列，即先查到結果集之後再加上去的一個列(強調：先有結果集後再添加偽列)。

簡單的說 rownum 是對符合條件結果的序號。它總是從1開始排起的。不能對rownum用">"。它與rownum實現的機制是有關的。同時， 也無法使用rownum = n（n>1的自然數）。rownum都是從1開始。 

分頁方法3 ： 使用rowid

SQL> select * from emp where rowid in ( select rid from (select rowid as rid, rownum as id from emp t where rownum < 15) where id >= 10 );

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

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

 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

 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

 

方法四：流量分析函數

SQL> select * from ( select t.* , row_number() over(order by ename) as num from emp t ) where num between 10 and 15;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NUM

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

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

 

方法五：使用rownum偽列查詢所有記錄後，外層使用between and 或 大於 小於

SQL> select * from (select t.* , rownum as num from emp t) where num between 10 and 15;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NUM

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

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30 10

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20 11

7900 JAMES CLERK 7698 1981/12/3 950.00 30 12

7902 FORD ANALYST 7566 1981/12/3 3000.00 20 13

7934 MILLER CLERK 7782 1982/1/23 1300.00 10 14

oracle sql 分頁