ORACLE分页查询SQL语法

--1:无ORDER BY排序的写法。(效率最高)
--(经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！)

SELECT*

 FROM (SELECTROWNUM AS rowno, t.*

FROMemp t

WHEREhire_date BETWEENTO_DATE ('20060501','yyyymmdd')

ANDTO_DATE ('20060731','yyyymmdd')

ANDROWNUM <= 20) table_alias

WHEREtable_alias.rowno>= 10;

--2:有ORDER BY排序的写法。(效率最高)
--(经过测试，此方法随着查询范围的扩大，速度也会越来越慢哦！)

SELECT*

FROM(SELECT tt.*,ROWNUM AS rowno

FROM(  SELECT t.*

FROMemp t

WHEREhire_date BETWEENTO_DATE ('20060501','yyyymmdd')

ANDTO_DATE ('20060731','yyyymmdd')

ORDERBY create_timeDESC, emp_no)tt

WHEREROWNUM <= 20) table_alias

WHEREtable_alias.rowno>= 10;

--3:无ORDER BY排序的写法。

select * from (select view1.*,rownum rn from (select p.* from passvehicleinfo p order by p.passvehicleid desc) view1 where rownum<10000) view2 where rn>9980;

下面最主要介绍第三种：按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;