oracle+先排序+再+rownum,关于oracle中rownum的使用探讨

关于oracle中rownum的使用探讨

一、rownum的使用

比对下面两个语句的异同

SELECT *

FROM (SELECT  a.*

FROM torderdetail a

ORDER BY order_date DESC)

WHERE ROWNUM <= 10

和

SELECT  a.*

FROM torderdetail a

WHERE ROWNUM <= 10

ORDER BY order_date DESC

之所以会出现这样的语句，主要是从效率上的考虑，前面条语句，是要进行全表扫描后再排序，然后再取10条纪录，后一条语句则不会全表扫描，只会取出10条纪录，很明显后条语句的效率会高许多。

那为什么会有争议呢，那就在于在执行顺序上争议，是先执行排序取10条纪录，还是取10条纪录，再排序呢？两种顺序取出来的结果是截然相反的，先排序再取10条，就是取最近的10条，而先取10条，再排序，则取出的最早的10条纪录。对于此语句，普遍的认为执行顺序是先取10条纪录再排序的。所以此语句应该是错误。

但实际上并非如此，此语句的执行顺序和order by的字段有关系，如果你order by 的字段是主建，则是先排序，再取10条(速度比第一种语句快)，而排序字段不是主键时，是先取10条再排序，此时结果就与要求不一样了，所以第二种写法一定要在排序字段是主键的情况下才能保证结果正确。

二、rownum的扩展

Row_number() over()这个分析函数是从9I开始提供的，一般的用途和rownum差不多。

一般写法row_number() over( order by order_date desc) 生成的顺序和rownum的语句一样，效率也一样(对于同样有order by 的rownum语句来说)，所以在这种情况下两种用法是一样的。

而对于分组后取最近的10条纪录，则是rownum无法实现的，这时只有row_number可以实现，row_number() over(partition by 分组字段 order by 排序字段)就能实现分组后编号。

比如说要取近一个月的每天最后10个订单纪录

SELECT *

FROM (SELECT a.*,

ROW_NUMBER () OVER (PARTITION BY TRUNC (order_date) ORDER BY order_date DESC)rn FROM torderdetail a)

WHERE rn <= 10

Rownum的另类用法，有时候我们会遇到这种需求，要求输出当月的所有天数，许多人会烦恼，数据库里又没有这样的表，怎么输出一个月的所有天数呢？

用rownum就能解决：

SELECT    TRUNC (SYSDATE, 'MM') + ROWNUM - 1

FROM DUAL

CONNECT BY ROWNUM <= TO_NUMBER (TO_CHAR (LAST_DAY (SYSDATE), 'dd'))

三、

横向比较：SQL SERVER 2005中也引用了Row_number() over()这个分析函数，用法和oracle中是使用几乎完全一样

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20854791/viewspace-582307/，如需转载，请注明出处，否则将追究法律责任。