关于ROWNUM和ORDER BY的有关问题

2012-10-20

关于ROWNUM和ORDER BY的问题前两天同事发现网站上同一个商品在几页里都出现，然后查语句，发现有诡异的事情，

关于ROWNUM和ORDER BY的问题
前两天同事发现网站上同一个商品在几页里都出现，然后查语句，发现有诡异的事情，原来以为可能是物化视图的关系，不过今天我测试了下，是和ORDER BY 里出现并列数据有关
先举例说明下

SQL code

SELECT a.*,rownum  FROM (SELECT   good_id, good_nm, prc            FROM tb_ag001        ORDER BY prc) a

出来的结果

加了过滤

SQL code

SELECT *  FROM (SELECT   good_id, good_nm, prc            FROM tb_ag001        ORDER BY prc) WHERE ROWNUM <9

另外种写法

SQL code

SELECT *  FROM (SELECT   good_id, good_nm, prc            FROM tb_ag001        ORDER BY prc) WHERE ROWNUM <= 8

可以看到6-8项目不一样了，按道理rownum<=8和rownum<9结果是一样，但出现了不同，不过如果再包层，结果就一致了，并且是正确的

SQL code

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

[解决办法]
帮顶
[解决办法]
关注
UP
[解决办法]
关注下
可能和子查询在内存中的排序有关吧
值相同时，排序不稳定。执行相同查询的时候，会从共享池中调用之前的结果
因为调用了相同的数据集
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次排序结果不一定相同
这是我的猜测
[解决办法]
SELECT *
FROM (SELECT good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc)
WHERE ROWNUM <9

这里的rownum与嵌套内层的rownum不相同，外层的rownum与嵌套内层的rownum没有任何关系
[解决办法]
比如说下面这个语句
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条了
[解决办法]

学习.. 先收藏下，等会有空在研究下，周一就是事多...
[解决办法]
SELECT *
FROM (SELECT ROWNUM rn, good_id, good_nm, prc
FROM tb_ag001
ORDER BY prc) a
WHERE rn < 9

这样就行
在内层查询中把数据按顺序附上了序号，在外层引用的时候可以直接当成实际存在的列来用
[解决办法]
to hebo2005：

建议，你用分析函数，这样就排序就统一起来了。否则的话简单的oracle的rownum都需要嵌套3成，才可以排序的。
如下：

SQL code

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
[解决办法]
顶
[解决办法]
SQL code16: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          1133424 旋覆透白绣花毛巾X1                      0          2133428 左旋C新纪润白霜(7ml)                    0          3133430 左旋C美白保湿精乳                       0          4133528 美白晶澈爽肤水                          0          5133530 左旋C燕窝初雪调养面膜                   0          6133628 左旋C-燕窝初雪调养面膜8g*2              0          7133728 化妆包1个                               0          8133828 BICATO活菌酵母修护晚霜5g                0          9138068 蓝牙耳机*1128TF卡*1                     0         10138069 超速读卡器*1                            0         11138073 内置128兆TF卡天然水晶手链*1             0         12138074 内置128兆TF卡水晶手链                   0         13已选择13行。已用时间:  00: 00: 00.0016: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          1133424 旋覆透白绣花毛巾X1                      0          2133428 左旋C新纪润白霜(7ml)                    0          3133430 左旋C美白保湿精乳                       0          4133528 美白晶澈爽肤水                          0          5133530 左旋C燕窝初雪调养面膜                   0          6133628 左旋C-燕窝初雪调养面膜8g*2              0          7133728 化妆包1个                               0          8已选择8行。     -----------------这是正确的结果，因为后面的过滤条件用的是rn < 9。已用时间:  00: 00: 00.0116: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                0133424 旋覆透白绣花毛巾X1                      0133428 左旋C新纪润白霜(7ml)                    0133430 左旋C美白保湿精乳                       0133528 美白晶澈爽肤水                          0133530 左旋C燕窝初雪调养面膜                   0138074 内置128兆TF卡水晶手链                   0133728 化妆包1个                               0已选择8行。    --------这是错误的结果。区别在于133628和138074这两条记录。已用时间:  00: 00: 00.0116: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
[解决办法]
在里层的查询，为什么不把rownum作为一个字段查出来？
[解决办法]
关注中！！！
------解决方案--------------------  
探讨
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  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是在当前查询检索完之后才产生的，所以用它做条件，每次检索出来的结果随即性比较大


[解决办法]
探讨
SELECT *
   FROM (SELECT  ROWNUM rn, good_id, good_nm, prc
                     FROM tb_ag001
                 ORDER BY prc) a
  WHERE rn  < 9

 这样就行
 在内层查询中把数据按顺序附上了序号，在外层引用的时候可以直接当成实际存在的列来用

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

热点排行