oracle分页查询

 

        1. rownum 分页

        SELECT * FROM emp;

        2. 显示rownum[oracle分配的]

        SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e;rn相当于Oracle分配的行的ID号

        3.挑选出6—10条记录

        先查出1-10条记录

        SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10;

        如果后面加上rownum>=6是不行的，

        4. 然后查出6-10条记录

        SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6;

       5. 几个查询变化

       a. 指定查询列，只需要修改最里层的子查询

       只查询雇员的编号和工资

       SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp)

e WHERE ROWNUM <= 10) WHERE rn >= 6;

        b. 排序查询，只需要修改最里层的子查询

       工资排序后查询6-10条数据

        SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER

by sal) e WHERE ROWNUM <= 10) WHERE rn >= 6;