八、oracle 分頁

標籤：

oracle的分頁一共有三種方式

方法一 根據rowid來分

SELECT *
  FROM EMP
 WHERE ROWID IN
       (SELECT RID
          FROM (SELECT ROWNUM RN, RID
                  FROM (SELECT ROWID RID, EMPNO FROM EMP ORDER BY EMPNO DESC)
                 WHERE ROWNUM <= ( (currentPage-1) * pageSize + pageSize )) --每頁顯示幾條
         WHERE RN > ((currentPage-1) * pageSize) ) --當前頁數
 ORDER BY EMPNO DESC;
 
eg、
-- 5 = (currentPage-1) * pageSize + pageSize   每頁顯示幾條
-- 0 = (currentPage-1) * pageSize              當前頁數
SELECT *
  FROM EMP
 WHERE ROWID IN
       (SELECT RID
          FROM (SELECT ROWNUM RN, RID
                  FROM (SELECT ROWID RID, EMPNO FROM EMP ORDER BY EMPNO DESC)
                 WHERE ROWNUM <= ( (1-1) * 5 + 5 )) --每頁顯示幾條
         WHERE RN > ((1-1) * 5) ) --當前頁數
 ORDER BY EMPNO DESC;

          

方法二 按分析函數來分

SELECT *
FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY empno DESC) RK FROM emp T)
WHERE RK <= ( (currentPage-1) * pageSize + pageSize ) --每頁顯示幾條
AND RK > ( (currentPage-1) * pageSize ); --當前頁數

eg、
-- 5 = (currentPage-1) * pageSize + pageSize   每頁顯示幾條
-- 0 = (currentPage-1) * pageSize              當前頁數
SELECT *
FROM (SELECT T.*, ROW_NUMBER() OVER(ORDER BY empno DESC) RK FROM emp T)
WHERE RK <= 5
AND RK > 0;

               

方法三 按rownum 來分

SELECT *
  FROM (SELECT T.*, ROWNUM RN
          FROM (SELECT * FROM EMP ORDER BY EMPNO DESC) T
         WHERE ROWNUM <= ( (currentPage-1) * pageSize + pageSize )) --每頁顯示幾條
 WHERE RN > ( (currentPage-1) * pageSize ); --當前頁數
           
eg、
-- 5 = (currentPage-1) * pageSize + pageSize   每頁顯示幾條
-- 0 = (currentPage-1) * pageSize              當前頁數
SELECT *
  FROM (SELECT T.*, ROWNUM RN
          FROM (SELECT * FROM EMP ORDER BY EMPNO DESC) T
         WHERE ROWNUM <= 5)
 WHERE RN > 0;

其中emp為表名稱，empno 為表的主鍵id，擷取按empno降序排序後的第1-5條記錄，emp表有70000 多條記錄。
個人感覺方法一的效率最好，方法三 次之，方法二 最差。

               

下面通過方法三來分析oracle怎麼通過rownum分頁的

1、
SELECT * FROM emp;
2、顯示rownum，由oracle分配的
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e; --rn相當於Oracle分配的行的ID號 
3、先查出1-10條記錄
正確的: SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM<=10;
錯誤的：SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE rn<=10;
4、然後查出6-10條記錄
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM<=10) WHERE rn>=6;

八、oracle 分頁