八、oracle 分页

方法一 根据rowid来分
<code>
SELECT *FROM EMPWHERE ROWID IN(SELECT RIDFROM (SELECT ROWNUM RN, RIDFROM (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 EMPWHERE ROWID IN(SELECT RIDFROM (SELECT ROWNUM RN, RIDFROM (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;
</code>

方法二 按分析函数来分

<code>
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;
</code>

方法三 按rownum 来分

<code>
SELECT FROM (SELECT T., ROWNUM RNFROM (SELECT * FROM EMP ORDER BY EMPNO DESC) TWHERE 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 RNFROM (SELECT * FROM EMP ORDER BY EMPNO DESC) TWHERE ROWNUM <= 5)WHERE RN > 0;
</code>

其中emp为表名称，empno 为表的主键id，获取按empno降序排序后的第1-5条记录，emp表有70000 多条记录。个人感觉方法一的效率最好，方法三 次之，方法二 最差。

下面通过方法三来分析oracle怎么通过rownum分页的
<code>
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;
</code>