Oracle 分页查询，排序分页

• 效率最高 内查询小于等于 外查询大于

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;