oracle关于rownum的使用【oracle】

时间:2024-03-12 16:27:28

转自:https://blog.csdn.net/qiuzhi__ke/article/details/78892822

关于rownum是怎么产生的(网上有不少的文章,下面是摘录):

rownum是在where条件过滤之后,在任何排序(order by)或聚集(aggregation)之前赋给行的。同时,只有当rownum被分配给行后才会递增。rownum的初始值为1。rownum在查询中产生后就不再变化:

select * from emp where ROWNUM <= 5 order by sal desc;

该语句的目的是想返回top 5薪水最高的员工信息,但根据rownum的产生原理,rownum在order by之前就已经产生,所以该语句并不能起到top 5的作用,正确的语法如下:

select * from (select * from emp order by sal desc) where ROWNUM <= 5;

rownum是Oracle的一个伪列,它的顺序根据从表中获取记录的顺序递增,这里要注意的是:由于记录在表中是无序存放的,因此你无法通过简单的rownum和order by的组合获得类似TOP N的结果。

因为ROWNUM是对结果集加的一个伪列,即先查到结果集之后再加上去的一个列 (强调:先要有结果集)。简单的说rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1,而有其他大于1的值。

如果你想获得像top n那样的结果,必须使用子查询:

select * from (select * from test order by id) where rownum<=5;

如果你想获得第5行到第10行之间的数据,则必须再加一层子查询:

 select T.* from (select t.*,rownum rn from (select * from test order by id) t where rownum<=10) T where T.rn>5;

其实上面的写法是由陷阱的,不信你把order by id换成order by name

你会惊奇的发现id=4这条数据出现在了两个地方,这不合逻辑!但事实就是这样的,为什么呢?因为name不唯一,两次排序取出的结果有可能会不一样,我还是举个例子吧:

 select id,name,rank() over(order by name) from test;

        ID NAME                RANK()OVER(ORDERBYNAME)
---------- -------------------- -----------------------
        1 A                                          1
        2 B                                          2
        6 C                                          3
        3 C                                          3
        4 C                                          3
        8 C                                          3
        5 C                                          3
        7 C                                          3
        9 D                                          9
        10 D                                          9

从上面的结果我们不难发现,根据name排序,有多条数据并列排在第3位,这样,当取前5名时,到底在并列第3中取哪几位就不是确定的事,因此就出现了之前出现的诡异的问题。那么,怎样才能彻底解决这个问题呢?其实只要在order by name后面加上rowid,保证不会出现并列的情况就可以了

排序列不唯一所带来的问题

 如果用来排序的列不唯一,也就是存在值相等的行,可能会造成第一次在前10条返回记录中,某行数据出现了,而第二次在11到第20条记录中,某行数据又出现了。一条数据重复出现两次,就必然意味着有数据在两次查询中都不会出现。
 
 其实造成这个问题的原因很简单,是由于排序列不唯一造成的。Oracle这里使用的排序算法不具有稳定性,也就是说,对于键值相等的数据,这种算法完成排序后,不保证这些键值相等的数据保持排序前的顺序。
 
 解决这个问题其实也很简单。有两种方法可以考虑。

1)在使用不唯一的字段排序时,后面跟一个唯一的字段。

一般在排序字段后面跟一个主键就可以了,如果表不存在主键,跟ROWID也可以。这种方法最简单,且对性能的影响最小。

2)另一种方法就是使用前面给出过多次的BETWEEN AND的方法。

 这种方式由于采用表数据的全排序,每次只取全排序中的某一部分数据,因此不会出现上面提到的重复数据问题。

 但是正是由于使用了全排序,而且ROWNUM信息无法推到查询内部,导致这种写法的执行效率很低

 

分页查询格式1
在查询的最外层控制分页的最小值和最大值。查询语句如下:  
SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
)
WHERE RN BETWEEN 21 AND 40

 

分页查询格式2 
SELECT * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

 

分页查询格式3
考虑到多表联合的情况,如果不介意在系统中使用HINT的话,可以将分页的查询语句改写为: 
SELECT /*+ FIRST_ROWS */ * FROM 
(
SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A 
WHERE ROWNUM <= 40
)
WHERE RN >= 21

效率问题
 对比这两种写法,绝大多数的情况下,第2个查询的效率比第1个高得多。

 

分页查询格式4

利用row_number()函数

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (ORDER BY paginator, id) AS rn
        FROM    mytable t
        )
WHERE   rn BETWEEN 900001 AND 900010

 

分页查询格式5

SELECT  *
FROM    (
        SELECT  t.*, ROWNUM AS rn
        FROM    (
                SELECT  * FROM mytable ORDER BY paginator, id
                ) t
        )
WHERE   rn >= 900001
        AND rownum <= 10

 

oracle中row_number()

1、row_number() over (order by col_1[,col_2 ...])
按照col_1[,col_2 ...]排序,返回排序后的结果集,并且为每一行返回一个不相同的值。

2、row_number() over (partition by col_n[,col_m ...] order by col_1[,col_2 ...])
先按照col_n[,col_m ...进行分组,再在每个分组中按照col_1[,col_2 ...]进行排序(升序),最后返回排好序后的结果集

row_number()over(partition by col1 order by col2)表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)。 与rownum的区别在于:使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序,而row_number()在包含排序从句后是先排序再计算行号码。

 

其他参考:

oracle中rownum和row_number()

http://www.jb51.net/article/65960.htm

oracle中row_number和rownum的区别和联系(翻译)

https://www.cnblogs.com/jcz1206/p/4378076.html