(转) ROWNUM与ORDER BY联用时对结果集的影响

最新推荐文章于 2024-09-23 20:03:06 发布

mmclxq2

最新推荐文章于 2024-09-23 20:03:06 发布

阅读量215

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： Oracle

本文链接：https://blog.youkuaiyun.com/mmclxq2/article/details/84361860

Oracle 专栏收录该内容

4 篇文章

订阅专栏

本文深入解析了在SQL查询中使用ROWNUM与ORDERBY时的不同行为，包括ROWNUM单独使用、与ORDERBY结合使用以及ORDERBY字段为PRIMARKEY或非PRIMARYKEY时的表现。通过实例演示，帮助理解ROWNUM与ORDERBY如何影响数据检索顺序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

开贴讲一下自己的理解

先建一个表
   CREATE TABLE TEST(ID NUMBER PRIMARY KEY NOT NULL, NAME VARCHAR2(15));
      /
      INSERT INTO TEST VALUES(1, 'John');
      INSERT INTO TEST VALUES(4, 'Tim');
      INSERT INTO TEST VALUES(2, 'Julie');
      INSERT INTO TEST VALUES(3, 'Stacy');
      INSERT INTO TEST VALUES(6, 'Rahul');
      INSERT INTO TEST VALUES(8, 'Leena');
      INSERT INTO TEST VALUES(7, 'Amy');
      INSERT INTO TEST VALUES(11, 'Bill');
      INSERT INTO TEST VALUES(9, 'Teri');
      INSERT INTO TEST VALUES(5, 'Julie');
      /

注意ID为2和ID为5的NAME是一样的, 有意为之

1, 当ROWNUM单独使用时, 它是按照数据的物理存储位置来赋予NUM号的,
根据ROWID, 我们可以加以验证, 如下SQL:
      SQL> SELECT ID, NAME, ROWID FROM TEST WHERE  ROWNUM < 6 ;

            ID NAME          ROWID
      ---------- --------------- ------------------
               1 John             AAADm2AABAAAGJiAAA
               4 Tim             AAADm2AABAAAGJiAAB
               2 Julie             AAADm2AABAAAGJiAAC
               3 Stacy          AAADm2AABAAAGJiAAD
               6 Rahul          AAADm2AABAAAGJiAAE

注意ROWID的顺序

2, 当ROWNUM与ORDER BY联合使用时, 分两种情况:
a,) 当ROWNUM与ORDER BY处于同一查询语句时, Oracle 是先给记录一个ROWNUM再进行ORDER BY,
      如下情况:
      SQL> SELECT ID, NAME, ROWID FROM TEST WHERE  ROWNUM < 6  ORDER BY NAME;

               ID NAME          ROWID
      ---------- --------------- ------------------
               1 John             AAADm2AABAAAGJiAAA
               2 Julie             AAADm2AABAAAGJiAAC
               6 Rahul          AAADm2AABAAAGJiAAE
               3 Stacy          AAADm2AABAAAGJiAAD
               4 Tim             AAADm2AABAAAGJiAAB

   注意ROWID, 先取前面5笔记录 AAADm2AABAAAGJiAAA 至 AAADm2AABAAAGJiAAE
   再ORDER BY

b,) 当ROWNUM与ORDER BY不处于同一查询语句,ORDER BY在子查询而ROWNUM在外层时,
      Oracle是先对记录一个ROWNUM再进行ORDER BY再给记录赋予一个ROWNUM.
      如下情况:
      SQL> SELECT *
         2  FROM (SELECT ID, NAME, ROWID FROM TEST ORDER BY NAME)
         3  WHERE  ROWNUM < 6;

               ID NAME          ROWID
      ---------- --------------- ------------------
               7 Amy             AAADm2AABAAAGJiAAG
               11 Bill             AAADm2AABAAAGJiAAH
               1 John             AAADm2AABAAAGJiAAA
               2 Julie             AAADm2AABAAAGJiAAC
               5 Julie             AAADm2AABAAAGJiAAJ

         这个,...呃, 咋说呢, 一目了然

3, 当ROWNUM与ORDER BY处于同一查询语句时, 根据ORDER BY的字段是否为PRKMARY KEY, 又分两种情况.

先删除TEST中的记录, 再INSERT如下记录
   INSERT INTO TEST VALUES(99, 'John');
      INSERT INTO TEST VALUES(1, 'John');
      INSERT INTO TEST VALUES(4, 'Tim');
      INSERT INTO TEST VALUES(2, 'Julie');
      INSERT INTO TEST VALUES(3, 'Stacy');
      INSERT INTO TEST VALUES(6, 'Rahul');
      INSERT INTO TEST VALUES(8, 'Leena');
      INSERT INTO TEST VALUES(7, 'Amy');
      INSERT INTO TEST VALUES(11, 'Bill');
      INSERT INTO TEST VALUES(9, 'Teri');
      INSERT INTO TEST VALUES(5, 'Julie');

      留意上面蓝色部分
a,) 当ORDER BY字段为PRIMARY KEY时, 此时Oracle是先ORDER BY再对记录ROWNUM
         如下SQL:
         SQL> SELECT ID, NAME, ROWID FROM TEST WHERE  ROWNUM < 6  ORDER BY ID;

               ID NAME          ROWID
         ---------- --------------- ------------------
                  1 John             AAADm5AABAAAGJiAAB
                  2 Julie             AAADm5AABAAAGJiAAD
                  3 Stacy          AAADm5AABAAAGJiAAE
                  4 Tim             AAADm5AABAAAGJiAAC
                  5 Julie          AAADm5AABAAAGJiAAK

         再看看ID为99的记录的ROWID
         SQL> SELECT ID, NAME, ROWID FROM TEST;

                  ID NAME          ROWID
         ---------- --------------- ------------------
                  99 John          AAADm5AABAAAGJiAAA
                  1 John             AAADm5AABAAAGJiAAB
                  4 Tim             AAADm5AABAAAGJiAAC
                  2 Julie             AAADm5AABAAAGJiAAD
                  3 Stacy          AAADm5AABAAAGJiAAE
                  6 Rahul          AAADm5AABAAAGJiAAF
                  8 Leena          AAADm5AABAAAGJiAAG
                  7 Amy             AAADm5AABAAAGJiAAH
                  11 Bill             AAADm5AABAAAGJiAAI
                  9 Teri             AAADm5AABAAAGJiAAJ
                  5 Julie             AAADm5AABAAAGJiAAK

      注意粉红色部分, 应该可以看出ID为99的记录最先INSERT, 其ROWID最小,
      但由于其ID最大, 且ID为PRIMARY KEY, 因此在
   SELECT ID, NAME, ROWID FROM TEST WHERE  ROWNUM < 6  ORDER BY ID
         先对ID做了ORDER BY操作, 再对操作后的结果集赋ROWNUM,
         然后取出符合条件(ROWNUM < 6)的值

b,) 当ORDER BY字段不为PRIMARY KEY时, 参照我写的第 1 种情况

转自http://www.itpub.net/thread-961972-1-1.html