Oracle分页查询

最新推荐文章于 2018-11-02 00:06:27 发布

weixin_30257433

最新推荐文章于 2018-11-02 00:06:27 发布

阅读量66

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/nuaa/p/3691754.html

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;

转载于:https://www.cnblogs.com/nuaa/p/3691754.html