关于oracle rownum分页 orderby一些问题求教

Question

--drop table TABLE_A;
--commit;
create table TABLE_A
(
  id      INTEGER,
  same    VARCHAR2(50),
  orderby VARCHAR2(50)
);
commit;
insert into table_a values(1,'same','A');
insert into table_a values(2,'same','B');
insert into table_a values(3,'same','C');commit;
delete from table_a where id='2';
insert into table_a values(2,'same','B');
commit;select aa.*, rownum
  from (select a.id, a.same, a.orderby from table_a a order by a.same) aa
 where rownum <= 2
union all
select bb.*
  from (select aa.*, rownum rn
          from (select a.id, a.same, a.orderby from table_a a order by a.same) aa) bb
 where bb.rn > 2
   and bb.rn <= 3;
我在本地oracle上执行这段代码，出现的结果是 ACC；与我本意想看到的ABC不一样。麻烦个人达人看看是什么原因。多谢。
我目前大概猜测的原因是order by的问题，因为目前的order by 相当于无效的，而我delete再插入的那条数据，就会丢失了。 如果我不加order by 会默认按照rowid排序能出来，如果我order by orderby字段也会出来。 

Answer 1

你的order by是根据字段same,而字段same内容全部是一样的……你应该用 order by orderby

Answer 2

上面只是模拟的一个情况 ，那为什么same一样就出问题呢 其实想得到是这个解释。 假如我遇到一个业务，数据存在父子级的关系，各个自己有不同的type，我想使用同一个sql来查询。

Answer 3

上面只是模拟的一个情况 ，那为什么same一样就出问题呢 其实想得到是这个解释。 假如我遇到一个业务，数据存在父子级的关系，各个自己有不同的type，我想使用同一个sql来查询。你这里只有3条数据，你如果多插几条数据试试？ 你order by same 得到的结果根本就不是绝对的。。

Answer 4

上面只是模拟的一个情况 ，那为什么same一样就出问题呢 其实想得到是这个解释。 假如我遇到一个业务，数据存在父子级的关系，各个自己有不同的type，我想使用同一个sql来查询。你这里只有3条数据，你如果多插几条数据试试？ 你order by same 得到的结果根本就不是绝对的。。我发现这个问题的时候 数据应该是28条。

Answer 5

drop table TABLE_A;
commit;
create table TABLE_A
(
  id      INTEGER,
  type    VARCHAR2(50),
  orderby VARCHAR2(50)
);
commit;
insert into table_a values(1,'type1','A');
insert into table_a values(2,'type1','B');
insert into table_a values(3,'type1','C');
insert into table_a values(4,'type2','D');
/*insert into table_a values(5,'type2','E');*/commit;
delete from table_a where id='2';
insert into table_a values(2,'type1','B');
commit;
 select aa.*, rownum rn
   from (select a.id, a.TYPE, a.orderby
           from table_a a        
          order by a.TYPE) aa
  where rownum <= 2
union all
select bb.*
  from (select aa.*, rownum rn
          from (select a.id, a.TYPE, a.orderby
                  from table_a a                 
                 order by a.TYPE) aa) bb
 where bb.rn > 2
   and bb.rn <= 3;
我发现如果是这种情况，ID为4那条 不插入的话就是跟最上面的情况类似；多了ID=4的这行，结果却还是一样， 但是如果我再插入ID=5的这行就会出现完整的数据了。 目前我的解决方法是在查询第一页的时候 也类似后面的一样多加了一次嵌套。

Answer 6

"Unless and until you add 'order by' to a query, you cannot say ANYTHING about the order of the rows returned.  Well, short of 'you cannot rely on the order of the rows being returned'"-- Tom Kytehttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11%5FQUESTION%5FID:3083286970877

Answer 7

首先非常谢谢你的耐心解答，其实这个问题是一次无意中操作发现的问题，就是我在更新数据的时候，进行的操作不是update，而是delete再次insert；然后我查询的时候就发现数据丢失的情况了。
业务的话可能不太好说明，就譬如这样说好了： 我有一个数据表用户表，其中用户分为两大类，教师跟学生，我想查询的结果按照这个类型排序，那么我先增加了20条学生，结果我更新了其中一条(delete再insert），那么我这时候分页查询就会出现这种情况了。
因为之前写的分页查询，第一页一直是2层，后面页数3层嵌套；
所以我怀疑是不是我上面的sql写法误入歧途了，写的不规范，但是因为对这块了解不是很多，所以想看看能否找到具体的错误点是什么，好日后避免这个问题。

Answer 8

看到一个解释:
“我们对于翻页等逻辑必须默认加上order by排序，而且order by的字段如果有重复值，必须指定第二排序字段，如果第二排序字段还有重复值，那必须指定更多的字段，直到所有的排序字段能够指定唯一顺序。”
或许这个是合理的解释是吗？

Answer 9

  SELECT AA.*, ROWNUM RN
    FROM (SELECT A.ID, A.TYPE, A.ORDERBY FROM TABLE_A A ORDER BY A.ID) AA
   WHERE ROWNUM <= 2
   UNION ALL SELECT BB.* FROM
   (SELECT AA.*, ROWNUM RN
            FROM (SELECT A.ID, A.TYPE, A.ORDERBY
                    FROM TABLE_A A
                   ORDER BY A.TYPE) AA) BB
   WHERE BB.RN > 2
     AND BB.RN <= 3;
执行的结果为：ABC