- 效率最高 内查询小于等于 外查询大于
select *
from (select t.*, rownum as n from STUDENT t where rownum <= 4) t
where t.n > 2 order by t.id desc;
- 查看执行计划
explain plan for
select * from (select rownum as n,d.* from dept d where rownum<=4) t where t.n>2;
select * from table(dbms_xplan.display());
- as 给列加别名 表名不能用as 加别名
- 有排序的分页,先排序,在查rownum 在用范围过滤 两个子查询 总结就是先查结果集,分页放到最后处理
FROM
(
SELECT
rownum n,
a.*
FROM
(
SELECT
*
FROM
STUDENT s
ORDER BY
s.CREATE_TIME DESC) a) b
WHERE
b.n <= 4
AND
b.n>2;