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
两层嵌套 ==三层嵌套???
1 select t2.* from (
2 select t.*, rownum as row_num from t where rownum<=20 order by ID asc
3 ) t2 where t2.row_num>10
4
5 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语句实现分页排序
1 select t3.* from (
2 select t2.*, rownum as row_num from (
3 select * from t order by t.id asc
4 ) t2 where rownum<=20
5 ) t3
6 where t2.row_num>11
7 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-----说白啦就是用()改变函数的优先级!
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大于给定值的记录