oracle关于rownum的使用【oracle】

转自：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