步骤:
第一层:构建我们所要查询字段信息并排序;
第二层:构建 rownum 别名 rn
第三层:加 where 条件, rn>=M and rn <=N
例:按照入职时间的先后顺序,查询从第 7 至第 10 个人是谁?
第一步:构建我们所要查询字段信息并排序;
(select ename,hiredate from emp order by hiredate) t1
第二步:构建 rownum 别名 rn
(select t1.*,rownum rn
from (select ename,hiredate from emp order by hiredate) t1) t2
第三步:加 where 条件, rn>=M and rn <=N
select * from (select t1.*,rownum rn
from (select ename,hiredate
from emp order by hiredate) t1) t2
where rn>=7 and rn<=10;
示例:
按照薪水高低的先后顺序,查询从第 7 至第 10 个人是谁?
SQL> select t2.* from (select t1.* ,rownum rn
2 from (select ename ,sal
3 from emp order by sal) t1) t2
4 where rn<=10 and rn>=7;
ENAME SAL RN
TURNER 1500.00 7
CLARK 2450.00 8
BLAKE 2850.00 9
JONES 2975.00 10