关于ROWNUM和ORDER BY的问题

Question

前两天同事发现网站上同一个商品在几页里都出现，然后查语句，发现有诡异的事情，原来以为可能是物化视图的关系，不过今天我测试了下，是和ORDER BY 里出现并列数据有关
先举例说明下
SELECT a.*,rownum
  FROM (SELECT   good_id, good_nm, prc
            FROM tb_ag001
        ORDER BY prc) a
出来的结果
加了过滤
SELECT *
  FROM (SELECT   good_id, good_nm, prc
            FROM tb_ag001
        ORDER BY prc)
 WHERE ROWNUM <9
另外种写法
SELECT *
  FROM (SELECT   good_id, good_nm, prc
            FROM tb_ag001
        ORDER BY prc)
 WHERE ROWNUM <= 8
可以看到6-8项目不一样了，按道理rownum<=8和rownum<9结果是一样，但出现了不同，不过如果再包层，结果就一致了，并且是正确的
SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (SELECT   good_id, good_nm, prc
                    FROM tb_ag001
                ORDER BY prc) a)
 WHERE rn < 9
SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (SELECT   good_id, good_nm, prc
                    FROM tb_ag001
                ORDER BY prc) a)
 WHERE rn <=8

Answer 1

关注下
可能和子查询在内存中的排序有关吧
值相同时，排序不稳定。执行相同查询的时候，会从共享池中调用之前的结果
因为调用了相同的数据集
SELECT a.*, ROWNUM rn 
          FROM (SELECT  good_id, good_nm, prc 
                    FROM tb_ag001 
                ORDER BY prc) a
因此rn<=8和rn<9是一样的至于前面的写法，
 SELECT   good_id, good_nm, prc
            FROM tb_ag001
        ORDER BY prc
order by会在内存中重新排序？因此2次排序结果不一定相同
这是我的猜测

Answer 2

SELECT *
  FROM (SELECT   good_id, good_nm, prc
            FROM tb_ag001
        ORDER BY prc)
 WHERE ROWNUM <9这里的rownum与嵌套内层的rownum不相同，外层的rownum与嵌套内层的rownum没有任何关系

Answer 3

SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (SELECT   good_id, good_nm, prc
                    FROM tb_ag001
                ORDER BY prc) a)
 WHERE rn < 9
会不会是因为上面这个查询比下面这个查询多了一层查询 也就多了一次排序所导致的结果
SELECT *
  FROM (SELECT   good_id, good_nm, prc
            FROM tb_ag001
        ORDER BY prc)
 WHERE ROWNUM <9

Answer 4

比如说下面这个语句
SELECT * 
  FROM (SELECT  good_id, good_nm, prc 
            FROM tb_ag001 
        ORDER BY prc) 
WHERE ROWNUM <9
红色部分查询出来的结果是经过ORDER BY 是有序的
所以结果就是从有序的结果集合中取出前9条再看看另外一个
SELECT * 
  FROM (SELECT a.*, ROWNUM rn 
          FROM ([color=#FF0000]SELECT  good_id, good_nm, prc 
                    FROM tb_ag001 
                ORDER BY prc) a[/color]) 
WHERE rn < 9 
红色部分出来的结果是有序的 可是经过绿色的一个查询 结果还一定保持有序吗
如果已经不是有序的话 那么外层再选出的前9条可能也就不是刚才的前9条了

Answer 5

SELECT *
  FROM (SELECT  ROWNUM rn, good_id, good_nm, prc
                    FROM tb_ag001
                ORDER BY prc) a
 WHERE rn < 9这样就行
在内层查询中把数据按顺序附上了序号，在外层引用的时候可以直接当成实际存在的列来用

Answer 6

SELECT *
  FROM (SELECT   good_id, good_nm, prc,rownum rn
            FROM tb_ag001
        ORDER BY prc)
 WHERE rn<9
和 
SELECT *
  FROM (SELECT   good_id, good_nm, prc,rownum rn
            FROM tb_ag001
        ORDER BY prc) WHERE rn<=8
这两个结果出来的是一样的，但不是我想要的结果

Answer 7

to hebo2005：建议，你用分析函数，这样就排序就统一起来了。否则的话简单的oracle的rownum都需要嵌套3成，才可以排序的。
如下：
SELECT *
FROM 
(
          SELECT a.*, ROWNUM rn
          FROM (SELECT   good_id, good_nm, prc
                    FROM tb_ag001
                ORDER BY prc --第一层，这是where条件层，这是必须的，这个时候取的是基本数据。
       ) a --第二层，在原来的结果集上进行列表，排需要，记录所有的rownum，以便于分页的时候取数据
 )b --第三层，真正的分页取数据层，想取第几页就取第几页。
 WHERE b.rn < 9当然，如果你没有这个思路逻辑的话，可以用分析函数来列记录行，这样就不会乱了，这个只需要2层就搞定了，如下：select a.*
from(
         SELECT   good_id, good_nm, prc
                 row_num() over(order by prc) rn--排序集合行号，这种排序的话，你外面是<9或者<=8结果都是一样的，而且这种排序效率比上一种还要高的。
        FROM tb_ag001
)a where a.rn<9【注释】：建议大家排序的时候，采用分析函数的行号，来进行排序分页，这样效率高些。
                

Answer 8

呵呵，我觉得你特别迷分析函数，分析函数不是万能的，因为里面也有ORDER BY，所以也会消耗不少时间，
其实对于这种语句用分析函数并不比rownum+order by 快，因为对大家来说是一样的要ORDER BY一下
分析函数比较适用于需要复杂计算的，可能需要多次自关联才能出来的环境，这样的分析函数的优势才会体现出来

Answer 9

另外，一般对于rownum的理解
select *
from (
select a.*
from a
order by prc
)
where rownum<=8select *
from (
select a.*,row_number() over(order by prc) rn
from a
)
where rn<=8这两句的结果及效率应该是一样的
实际上用row_number一样会出现以上的问题
SELECT *
  FROM (SELECT good_id, good_nm, prc, ROW_NUMBER () OVER (ORDER BY prc) rn
          FROM tb_ag001)
 WHERE rn <= 8SELECT *
  FROM (SELECT good_id, good_nm, prc, ROW_NUMBER () OVER (ORDER BY prc) rn
          FROM tb_ag001)
 WHERE rn <9

Answer 10

SELECT *
  FROM (SELECT   good_id, good_nm, prc
            FROM tb_ag001
        ORDER BY prc)
 WHERE ROWNUM <9这句有问题的关键是过滤条件WHERE ROWNUM < 9造成的这和ORDER BY没有太直接的联系，关键是ROWNUM的问题。

Answer 11

16:29:53 scott@TUNGKONG> SELECT a.*,rownum FROM (SELECT good_id, good_nm, prc FROM tb_ag001 ORDER BY prc) a;GOOD_I GOOD_NM                               PRC     ROWNUM
------ ------------------------------ ---------- ----------
133337 阳光宝贝防晒修复套SPF-30                0          1
133424 旋覆透白绣花毛巾X1                      0          2
133428 左旋C新纪润白霜(7ml)                    0          3
133430 左旋C美白保湿精乳                       0          4
133528 美白晶澈爽肤水                          0          5
133530 左旋C燕窝初雪调养面膜                   0          6
133628 左旋C-燕窝初雪调养面膜8g*2              0          7
133728 化妆包1个                               0          8
133828 BICATO活菌酵母修护晚霜5g                0          9
138068 蓝牙耳机*1128TF卡*1                     0         10
138069 超速读卡器*1                            0         11
138073 内置128兆TF卡天然水晶手链*1             0         12
138074 内置128兆TF卡水晶手链                   0         13已选择13行。已用时间:  00: 00: 00.00
16:30:06 scott@TUNGKONG> SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT good_id, good_nm, prc FROM tb_ag001 ORDER BY prc) a) WHERE rn < 9;GOOD_I GOOD_NM                               PRC         RN
------ ------------------------------ ---------- ----------
133337 阳光宝贝防晒修复套SPF-30                0          1
133424 旋覆透白绣花毛巾X1                      0          2
133428 左旋C新纪润白霜(7ml)                    0          3
133430 左旋C美白保湿精乳                       0          4
133528 美白晶澈爽肤水                          0          5
133530 左旋C燕窝初雪调养面膜                   0          6
133628 左旋C-燕窝初雪调养面膜8g*2              0          7
133728 化妆包1个                               0          8已选择8行。     -----------------这是正确的结果，因为后面的过滤条件用的是rn < 9。已用时间:  00: 00: 00.01
16:30:21 scott@TUNGKONG> SELECT * FROM (SELECT good_id, good_nm, prc FROM tb_ag001 ORDER BY prc) WHERE ROWNUM < 9;GOOD_I GOOD_NM                               PRC
------ ------------------------------ ----------
133337 阳光宝贝防晒修复套SPF-30                0
133424 旋覆透白绣花毛巾X1                      0
133428 左旋C新纪润白霜(7ml)                    0
133430 左旋C美白保湿精乳                       0
133528 美白晶澈爽肤水                          0
133530 左旋C燕窝初雪调养面膜                   0
138074 内置128兆TF卡水晶手链                   0
133728 化妆包1个                               0已选择8行。    --------这是错误的结果。区别在于133628和138074这两条记录。已用时间:  00: 00: 00.01
16:30:46 scott@TUNGKONG> SELECT rownum,a.* from tb_ag001 a order by prc;    ROWNUM GOOD_I GOOD_NM                               PRC
---------- ------ ------------------------------ ----------
         1 133337 阳光宝贝防晒修复套SPF-30                0
         2 133424 旋覆透白绣花毛巾X1                      0
         3 133428 左旋C新纪润白霜(7ml)                    0
         4 133430 左旋C美白保湿精乳                       0
         5 133528 美白晶澈爽肤水                          0
         6 133530 左旋C燕窝初雪调养面膜                   0
        13 133628 左旋C-燕窝初雪调养面膜8g*2              0    --------------
         8 133728 化妆包1个                               0
         9 133828 BICATO活菌酵母修护晚霜5g                0
        10 138068 蓝牙耳机*1128TF卡*1                     0
        11 138069 超速读卡器*1                            0
        12 138073 内置128兆TF卡天然水晶手链*1             0
         7 138074 内置128兆TF卡水晶手链                   0    --------------已选择13行。已用时间:  00: 00: 00.03

Answer 12

SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (SELECT   good_id, good_nm, prc
                    FROM tb_ag001
                ORDER BY prc) a)
 WHERE rn < 9SELECT * 
  FROM (SELECT  ROWNUM rn, good_id, good_nm, prc 
                    FROM tb_ag001 
                ORDER BY prc) a 
WHERE rn < 9 
这两句sql查询出的数据是一致的，要想顺序也一致，只有表的数据时按照prc从小到大insert进去才行，否则得话第二句最后还得加个ORDER BY prc，不过加上order后，执行速度肯定受影响
在最外层用rownum做条件是肯定不行的，因为rownum是在当前查询检索完之后才产生的，所以用它做条件，每次检索出来的结果随即性比较大

Answer 13

ROWNUM的产生是和数据的读取顺序有关的，也就是说ROWNUM是读出一条标记一条。
SELECT * FROM (SELECT good_id, good_nm, prc FROM tb_ag001 ORDER BY prc) WHERE ROWNUM < 9;执行这句的时候，ROWNUM < 9这个条件会把子查询中的数据过滤，子查询选出的数据是含138074的数据，所以最终的结果也就是含138074的数据了。

Answer 14

这两句的结果是不一致的。。第二句中产生的rn和第一句是不一致的，这个时候的rn已经等同于错误结果的rownum的效果了

Answer 15

在里层的查询，为什么不把rownum作为一个字段查出来？

Answer 16

因为涉及到ORDER BY的问题，如果不是主键排序
则是先ROWNUM排序，再ORDER BY
而语句的目地是为了得到排序后的结果后再分页

Answer 17

假设表test中有字段id和name
那么
select a.* from
(select * from test order by id) a;
查询出来的结果是有序的还是无序的呢

Answer 18

试过的，这样出来的<=8和<9的结果是一样的，但不是我要的结果，是先取ROWNUM，再排序的
上面我贴出来结果的
索引是在GOOD_ID上，这个没有重复的估计可能和并列排序情况下顺序读取数据有关吧

Answer 19

实在不行，就是在order by prc 后面再加上主键id好了，order by prc asc, id desc等等(最新的排在前面，所以主键id desc)。

Answer 20

order by 的是prc字段，在prc的值相同时，排序是不稳定的。
如果要得出个稳定的结果，须在最里层用个其它的字段固定住（如将rownum取出做为个字段），然后外层在进行加条件。

Answer 21

http://blog.csdn.net/liweiwei123456/article/details/7680821#html  希望对你有用。

Answer 22

http://blog.csdn.net/liweiwei123456/article/details/7680821#html 希望对你有用