关于ROWNUM和ORDER BY的问题

[hebo2005]

前两天同事发现网站上同一个商品在几页里都出现，然后查语句，发现有诡异的事情，原来以为可能是物化视图的关系，不过今天我测试了下，是和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

[liusong_china]

ROWNUM的产生是和数据的读取顺序有关的，也就是说ROWNUM是读出一条标记一条。

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

执行这句的时候，ROWNUM < 9这个条件会把子查询中的数据过滤，子查询选出的数据是含138074的数据，所以最终的结果也就是含138074的数据了。。。。

[liusong_china]

[Quote=引用 21 楼 superhsj 的回复:]
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是在当前查询检索完之后才产生的，所以用它做条件，每次检索出来的结果随即性比较大

[/Quote]

这两句的结果是不一致的。。。。。。。。

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

[ddalei]

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

[hebo2005]

[Quote=引用 24 楼 ddalei 的回复:]
在里层的查询，为什么不把rownum作为一个字段查出来？
[/Quote]
因为涉及到ORDER BY的问题，如果不是主键排序
则是先ROWNUM排序，再ORDER BY
而语句的目地是为了得到排序后的结果后再分页

[kan2007]

关注中！！！

[wh62592855]

[Quote=引用 21 楼 superhsj 的回复:]
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是在当前查询检索完之后才产生的，所以用它做条件，每次检索出来的结果随即性比较大

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

[inthirties]

[Quote=引用 12 楼 superhsj 的回复:]
SELECT *
  FROM (SELECT  ROWNUM rn, good_id, good_nm, prc
                    FROM tb_ag001
                ORDER BY prc) a
WHERE rn < 9

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

试过这个没有。

你的索引建立在哪里叻。

[hebo2005]

[Quote=引用 28 楼 inthirties 的回复:]
引用 12 楼 superhsj 的回复:
SELECT *
  FROM (SELECT  ROWNUM rn, good_id, good_nm, prc
                    FROM tb_ag001
                ORDER BY prc) a
  WHERE rn  < 9

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


试过这个没有。

你的索引建立在哪里叻。
[/Quote]
试过的，这样出来的<=8和<9的结果是一样的，但不是我要的结果，是先取ROWNUM，再排序的
上面我贴出来结果的
索引是在GOOD_ID上，这个没有重复的

估计可能和并列排序情况下顺序读取数据有关吧

[oraclemch]

[Quote=引用 29 楼 hebo2005 的回复:]
引用 28 楼 inthirties 的回复:
引用 12 楼 superhsj 的回复:
SELECT *
  FROM (SELECT  ROWNUM rn, good_id, good_nm, prc
                    FROM tb_ag001
                ORDER BY prc) a
  WHERE rn  < 9

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


试过这个没有。

你的索引建立在哪里叻。

试过的，这样出来的 <=8和 <9的结果是一样的，但不是我要的结果，是先取ROWNUM，再排序的
上面我贴出来结果的
索引是在GOOD_ID上，这个没有重复的

估计可能和并列排序情况下顺序读取数据有关吧
[/Quote]

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

[hebo2005]

[Quote=引用 30 楼 oraclemch 的回复:]
引用 29 楼 hebo2005 的回复:
引用 28 楼 inthirties 的回复:
引用 12 楼 superhsj 的回复:
SELECT *
  FROM (SELECT  ROWNUM rn, good_id, good_nm, prc
                    FROM tb_ag001
                ORDER BY prc) a
  WHERE rn  < 9

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


试过这个没有。

你的索引建立在哪里叻。

试过的，这样出来的 <=8和 <9的结果是一样的，但不是我要的结果，是先取ROWNUM，再排序的
上面我贴出来结果的
索引是在GOOD_ID上，这个没有重复的

估计可能和并列排序情况下顺序读取数据有关吧


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

主要是要搞清楚为什么会这样，解决办法是很多的

[碧水幽幽泉]

学习中。。。

[Adebayor]

帮顶

[iqlife]

关注
UP

[小灰狼W]

关注下
可能和子查询在内存中的排序有关吧
值相同时，排序不稳定。执行相同查询的时候，会从共享池中调用之前的结果
因为调用了相同的数据集
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次排序结果不一定相同
这是我的猜测

[zhangwonderful]

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

这里的rownum与嵌套内层的rownum不相同，外层的rownum与嵌套内层的rownum没有任何关系

[hebo2005]

[Quote=引用 3 楼 wildwave 的回复:]
关注下
可能和子查询在内存中的排序有关吧
值相同时，排序不稳定。执行相同查询的时候，会从共享池中调用之前的结果
因为调用了相同的数据集
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次排序结果不一定相同
这是我的猜测
[/Quote]

我多次运行的结果是一样，就是结果不是预计的，再包一层后才会正确

[vingo2000]

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

[zhq_442577358]

..........

[weiwei_good_java]

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

[weiwei_good_java]

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