分页查询语法模版:
select t2.*
from ( select t1.*, rownum rn
from (select *
from 表名) t1
where rownum<=大范围(取到多少条数据)) t2
where rn>=小范围(从第几条数据开始取);
特别说明:
oracle 分页查询是通过三层筛选法进行查询的。每一次都可以带 where 条件来对要查询的信息
进行筛选。
建议:
第一层:构建我们所要查询字段信息并排序;
第二层:构建 rownum 别名 rn
第三层:加 where 条件,rn>=M and rn <=N
例如:
按照入职时间的先后顺序,查询从第 7 至第 10 个人是谁?
分析:1)查询出 emp 中的所有信息,并按照 hiredate 字段排序;
select * from emp order by hiredate;
2)在第一步查询的结果中添加 rownum 字段并起别名 rn
select rownum rn, t1.*
from (select * from emp order by hiredate) t1
3)在第二步基础上查询所有并添加 where 过滤条件
select *
from (select rownum rn, t1.*
from (select *
from emp
order by hiredate) t1) t2
where rn>=7 and rn<=10;
或者:如下
SQL>select t2.ename,t2.hiredate,t2.rn
from (select t1.*,rownum rn
from (select *
from emp order by hiredate) t1
where rownum<=10) t2
where rn>=7;
SQL>select t2.*
from (select t1.*,rownum rn
from (select *
from emp) t1
where rownum<=10) t2
where rn>=5;
看得更清楚一点
select t2.* from
(select t1.*,rownum rn from
(select * from emp)
t1)
t2 where rn>=5 and rn<=10;
select t2.*
from ( select t1.*, rownum rn
from (select *
from 表名) t1
where rownum<=大范围(取到多少条数据)) t2
where rn>=小范围(从第几条数据开始取);
特别说明:
oracle 分页查询是通过三层筛选法进行查询的。每一次都可以带 where 条件来对要查询的信息
进行筛选。
建议:
第一层:构建我们所要查询字段信息并排序;
第二层:构建 rownum 别名 rn
第三层:加 where 条件,rn>=M and rn <=N
例如:
按照入职时间的先后顺序,查询从第 7 至第 10 个人是谁?
分析:1)查询出 emp 中的所有信息,并按照 hiredate 字段排序;
select * from emp order by hiredate;
2)在第一步查询的结果中添加 rownum 字段并起别名 rn
select rownum rn, t1.*
from (select * from emp order by hiredate) t1
3)在第二步基础上查询所有并添加 where 过滤条件
select *
from (select rownum rn, t1.*
from (select *
from emp
order by hiredate) t1) t2
where rn>=7 and rn<=10;
或者:如下
SQL>select t2.ename,t2.hiredate,t2.rn
from (select t1.*,rownum rn
from (select *
from emp order by hiredate) t1
where rownum<=10) t2
where rn>=7;
SQL>select t2.*
from (select t1.*,rownum rn
from (select *
from emp) t1
where rownum<=10) t2
where rn>=5;
看得更清楚一点
select t2.* from
(select t1.*,rownum rn from
(select * from emp)
t1)
t2 where rn>=5 and rn<=10;