實現oracle分頁---3種方法--

標籤：擷取   acl   主鍵   sel   class   page   blog   名稱   ora   

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 <= 5AND 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分頁---3種方法--