Oracle分分页一般查询格式: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21 为什么基于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 ,再取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: 1.不能在where语句中使用别名进行过滤) 2.Oracle中的rownum不能使用大于>的问题: 一、对rownum的说明 关于Oracle 的 rownum 问题,很多资料都说不支持SQL语句中的“>、>=、=、between...and”运算符,只能用如下运算符号“>、>=、!=”, 并非说用“>、>=、=、between..and”时会提示SQL语法错误,而是经常是查不出一条记录来,还会出现似乎是莫名其妙的结果来。 其实,只要理解好了这个 rownum 伪列的意义就不应该感到惊奇。 rowid 与 rownum 虽都被称为伪列,但它们的存在方式是不一样的: rowid 是物理存在的,表示记录在表空间中的唯一位置ID,在DB中是唯一的。只要记录没被搬动过,rowid是不变的。 rowid 相对于表来说又像表中的一般列,所以,以 rowid 为条件就不会有rownum那些莫名其妙的结果出现。 另外还要注意:rownum不能以任何基表的名称作为前缀。 对于下面的SQL语句 SQL>select rownum,id,age,name from loaddata where rownum > 2; ROWNUM ID AGE NAME ------- ------ --- ------ rownum>2,没有查询到任何记录。 因为rownum总是从1开始的,第一条不满足去掉的话,第二条的rownum 又成了1。依此类推,所以永远没有满足条件的记录。 可以这样理解:rownum是一个序列,是Oracle数据库从数据文件或缓冲区中读取数据的顺序。 它取得第一条记录则rownum值为1,第二条为2。依次类推。 当使用“>、>=、=、between...and”这些条件时,从缓冲区或数据文件中得到的第一条记录的rownum为1 ,不符合sql语句的条件,会被删除,接着取下条。 下条的rownum还会是1,又被删除,依次类推,便没有了数据。 参考博文: https://blog.csdn.net/feiyu8607/article/details/6887561 https://www.cnblogs.com/java0819/archive/2011/08/03/2146205.html