Oracle分页查询

RowNum:表示行号，实际上是一个列，这个列是伪列。

例子：

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp;

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp WHERE RowNum<=5;

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp WHERE RowNum BETWEEN 5 AND 10;(显示不出来)

如果想要进行中间的截取操作，则只能采用子查询，例如现在假设每页显示5条记录，第二页应该显示6-10条。那么对于数据库操作来讲，它在查询的时候应该首先查询出1-10条，之后再在查询的结果中取出。

SELECT * FROM(SELECT ROWNUM rn,empno,ename,job,sal,hiredate FROM emp WHERE RowNum<=10) temp WHERE temp.rn>5;

--每页5条

--查询第二页
SELECT *
  FROM (SELECT ROWNUM RN, EMPNO, ENAME, JOB, SAL, HIREDATE
          FROM EMP
         WHERE ROWNUM <= 2*5) TEMP
 WHERE TEMP.RN > (2-1)*5;
等于
SELECT *
  FROM (SELECT ROWNUM RN, T1.*
          FROM (SELECT * FROM EMP) T1
         WHERE ROWNUM <= 2 * 5)
 WHERE RN > (2 - 1) * 5;

 

分页查询模板

 SELECT *  FROM (SELECT ROWNUM RN, a1.* FROM ( SELECT EMPNO, ENAME, JOB, SAL, HIREDATE
          FROM EMP ORDER BY EMPNO) a1
         WHERE ROWNUM <= 2*5) TEMP
 WHERE TEMP.RN > (2-1)*5;

 

根据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 <= 3*5)
WHERE rn>(3-1)*5)ORDER BY EMPNO DESC;