Oracle分页查询，面试你值得关注！

皓月苍狼 2017-11-24 23:42:05

--分页查询(假设每页显示5条)

--不包含排序

--效率低

select * from (select rownum rn,d.* from tuser d) p where p.rn<=7 and p.rn>=3;

select * from (select rownum rn,d.* from tuser d) p where p.rn between 3 and 7;

--效率高

select * from (select rownum rn,d.* from tuser d where rownum<=7) p where p.rn>=3;

--包含排序

--排序+区域查询:（效率低）

select * from (select rownum rn,t.* from (select d.* from tuser d order by d.ID) t) p where p.rn<=7 and p.rn>=3;

select * from (select rownum rn,t.* from (select d.* from tuser d order by d.ID) t) p where p.rn between 3 and 7;

--排序+区间查询:（效率高）

select * from (select rownum rn,t.* from (select d.* from tuser d order by d.ID) t where rownum<=7) p where p.rn>=3;