Oracle分页查询

.按rownum来分*
select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980;
其中t_xiaoxi为表名称，cid为表的关键字段，取按cid降序排序后的第9981-9999条记录，t_xiaoxi表有70000多条记录。

//测试通过的分页查询okokok
select * from (select a1.*, rownum rn from(select ename,job from emp) a1 where rownum<=10)where rn>=5;

下面最主要介绍第三种：按rownum来分

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;
用查询结果创建新表.
这个命令是一种快捷的建表方式
CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename,
sal, job, deptno FROM emp;
创建好之后，desc mytable；和select * from mytable;看看结果如何？