[转]oracle分页用两层循环还是三层循环？

select t2.* from      --两层嵌套
　　(select t.* , rownum as row_numfrom t where rownum <=20) t2
　　where t2.row_num > 11

select t3.* from (      --三层嵌套
select t2.*, rownum as row_num from (
select * from t
) t2 where rownum<=20
) t3 
where t2.row_num>11

两层嵌套  ==三层嵌套？？？ 

select t2.* from (

  select t.*, rownum as row_num from t where rownum<=20 order by ID asc
) t2 where t2.row_num>10 

order by ID asc 

因为在查询的时候，order by 的执行是在 select 之后的，所以在第一层查询中，得到的结果可能是如下

 

ID   row_num 

1        3

8        20

20      4

21      1

...

100    8

===20条记录，其中row_num字段的值在1-20

 

 

这样的子结果集，在经过第二层过滤的时候，是得不到我们想要的结果的

 

 ID 

 11    

 12

 13

 ...

 20 

 

 所以需要用如下的sql语句实现分页排序

select t3.* from (
  select t2.*, rownum as row_num from (
     select * from t order by t.id asc
  ) t2 where rownum<=20
) t3 
where t2.row_num>11
order by t3.id asc  

为什么基于ROWNUM的oracle分页实现，要采用三层嵌套的方式？ 
1 首先，在没有order by clause的情况下，oracle的查询结果的顺序会是不确定的。如上面的例子。所以order by的使用是应该的，以免因为index等的原因导致不确定的results order。 
2 其次，在order by 和 ROWNUM同时使用时，oracle默认的策略是先为伪列rownum赋值，再order by。
引用
rownum与order by同时存在的问题 
当 where 后面有rownum的判断，并且存在order by时候，rownum的优先级高！ 
　　oracle会先执行rownum的判断，然后从结果中order by，很明显是错误的结果啦！就好像学校要取成绩最好的前10名同学，结果这种方法一执行，成了取出10名同学，然后按照成绩的高低排序！ 
　　这点与SQL Server的TOP完全不同，TOP遇上order by，是先执行order by，在分页的； 
　　解决办法就是先执行order by，然后嵌套执行rownum-----说白啦就是用()改变函数的优先级！

所以，第二层嵌套的目的就是：让结果先order by，再取rownum! 
3 再次，因为rownum不可使用 >(=) 来判断的原因，所以需要最外围的第三层嵌套

SELECT *
  FROM (SELECT *
          FROM (SELECT ROWNUM AS ROW_ID, ORDER_ID
                  FROM ORDERS
                 ORDER BY ORDER_ID DESC) A
         WHERE ROW_ID < 10)
 WHERE ROW_ID >= 5;

--上面这样写也是错误的，正如前面的原因，有order by 和rownum时，先给rownum赋值，然后再对结果进行order by，
   所以上面的语句同样是取rownum为5--10之间的记录，而不是排好序之后的5--10记录。
正确写法：

SELECT B.ORDER_ID,B.ORDER_DETAILS,B.R_ID
  FROM (SELECT A.ORDER_ID,A.ORDER_DETAILS,ROWNUM AS R_ID      --对排序好的结果取其rownum，再取rownum 小于给定值的记录（不能直接对rownum取大于）
          FROM (SELECT ORDER_ID,ORDER_DETAILS       --获取排序好的结果集
                  FROM ORDERS
                 ORDER BY ORDER_ID DESC) A
         WHERE ROWNUM < 10) B
 WHERE R_ID >= 5;                    --取R_ID大于给定值的记录

PS: 不能在where语句中使用别名进行过滤（汗一个，看来很多东西需要实践啊。）

http://www.cnblogs.com/binblog/archive/2012/01/02/2309991.html

http://www.xuebuyuan.com/1292664.html