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

本文深入解析了在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

<think>好的,我现在要回答用户关于OracleROWNUMORDER BY执行顺序及正确用法的问题。首先,我需要理解用户的问题。用户可能在使用分页查询遇到了结果不正确的情况,因为同使用了ROWNUMORDER BY,但结果顺序不符合预期。需要解释执行顺序以及如何正确使用这两个功能。 首先,根据引用内容,ROWNUM结果返回后分配的伪列,它在数据被检索之后才生成。而ORDER BY是在查询结果确定之后进行排序的。但是,当同使用ROWNUMORDER BY,执行顺序会影响最终结果。例如,如果先应用ROWNUM筛选再排序,会导致排序后的结果中的ROWNUM顺序混乱,因为ROWNUM是在排序前就分配好的。 根据引用中的例子,比如用户想要分页显示排序后的数据,直接使用WHERE ROWNUM < 10然后ORDER BY可能不会得到正确结果,因为ROWNUM是在排序前分配的。正确的做法应该是先排序整个结果,然后再应用ROWNUM进行分页。这候需要使用子查询,先进行排序,外层再对排序后的结果分配ROWNUM。例如,在引用4中提到的方法:先包一层子查询进行排序,再赋予ROWNUM值。 另外,引用5提到,直接使用ORDER BY不会改变原有ROWNUM的顺序,因此需要将排序后的结果作为子查询,然后在外层使用ROWNUM进行分页。例如,正确的写法应该是SELECT * FROM (SELECT ... ORDER BY ...) WHERE ROWNUM <= 10。 可能用户遇到的问题是在分页查询,比如想要第5到第10条排序后的数据,直接使用ROWNUM会导致错误,因为ROWNUM在排序前已经被分配。正确的做法是先排序,然后在外层使用ROWNUM,或者使用MINUS操作,如引用3中的例子,不过这种方法可能效率不高,尤其是数据量大。 总结来说,执行顺序是先处理WHERE子句,再应用ORDER BY,最后分配ROWNUM。但实际情况中,如果直接在同一个查询中使用ROWNUM条件和ORDER BY,数据库可能会先应用WHERE条件(包括ROWNUM过滤)再进行排序,导致排序的是被过滤后的结果,而不是整个数据。因此,正确的用法是将排序后的结果作为子查询,再在外层应用ROWNUM进行分页。 用户可能需要具体的示例来理解如何正确编写SQL语句。比如,引用4中的示例展示了如何先排序再分页,使用两层子查询,内层排序,外层分配ROWNUM。此外,还需要注意分页使用ROWNUM的范围,比如获取第6到第10条数据,可能需要在外层再包一层查询,使用BETWEEN或者>=和<=。 还需要提到常见的错误案例,比如直接SELECT * FROM table WHERE ROWNUM <=10 ORDER BY column,这样得到的10条数据是未经排序的,然后排序这10条,但用户期望的是先排序整个表再取前10条。这候就需要调整查询结构,先排序再取ROWNUM。 最后,可能需要提到在Oracle 12c及更高版本中,可以使用OFFSET-FETCH子句来简化分页查询,但传统方法仍然有效,并且需要理解执行顺序的原理。</think>### OracleROWNUMORDER BY的执行顺序及正确用法 #### **1. 执行顺序的核心问题** 在Oracle中,$ROWNUM$ 是结果的**伪列**,其值在数据检索过程中按行返回顺序逐行分配[^2]。而 $ORDER BY$ 子句是对最终结果的排序操作。两者的执行顺序直接影响查询结果: - **错误用法**:若直接同使用 $ROWNUM$ 和 $ORDER BY$,例如: ```sql SELECT * FROM employees WHERE ROWNUM <= 10 ORDER BY salary DESC; ``` 实际执行流程为:**先筛选前10行数据**,再对这10行进行排序。这会导致最终仅对部分数据排序,而非全局排序[^4][^5]。 - **正确逻辑**:需**先排序整个数据**,再分配 $ROWNUM$ 进行分页。 --- #### **2. 正确用法示例** ##### **场景:获取薪资最高的前10名员工** 1. **错误写法**(仅排序局部数据): ```sql SELECT * FROM employees WHERE ROWNUM <= 10 ORDER BY salary DESC; -- 结果不准确 ``` 2. **正确写法**(子查询先排序): ```sql SELECT * FROM ( SELECT * FROM employees ORDER BY salary DESC ) WHERE ROWNUM <= 10; ``` - **内层子查询**:先对全表按薪资降序排列。 - **外层查询**:对排序后的结果分配 $ROWNUM$ 并取前10条[^4][^5]。 --- #### **3. 分页查询的实现** ##### **场景:分页查询第6-10条数据(每页5条)** 需通过两层子查询实现: ```sql SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM employees ORDER BY salary DESC ) t WHERE ROWNUM <= 10 -- 结束行 ) WHERE rn >= 6; -- 起始行 ``` - **内层排序**:`t` 子查询完成全局排序。 - **中层分配ROWNUM**:为排序后的数据分配行号 `rn`。 - **外层分页**:通过 `rn` 控制分页范围[^4]。 --- #### **4. 常见错误解决方案** | **错误类型** | **现象** | **解决方案** | |----------------------------|----------------------------------|------------------------------------------| | 直接混合使用ROWNUMORDER BY | 分页结果未按预期排序 | 通过子查询先排序,再分配ROWNUM | | 使用ROWNUM过滤后排序 | 仅对部分数据排序导致逻辑错误 | 确保ORDER BY作用于完整数据 | --- #### **5. 性能优化建议** - **索引优化**:为排序字段(如 `salary`)建立索引,可加速全局排序过程。 - **窗口函数替代**(Oracle 12c+):使用 `OFFSET-FETCH` 语法简化分页: ```sql SELECT * FROM employees ORDER BY salary DESC OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY; -- 直接跳过前5条,取第6-10条 ``` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值