Rownum与Order by（一）

最新推荐文章于 2021-12-22 09:27:15 发布
最新推荐文章于 2021-12-22 09:27:15 发布
阅读量164 收藏
点赞数
文章标签： 数据库

rownum是数据表的一个虚列，是随着select获取到的数据集合而依次生成的数据项目。借助rownum，很多复杂的SQL需求都得以解决。但是，rownum绝不是一个数据列，使用它的时候一定要注意rownum的生成原理和特性。本篇就从rownum与order by结合，谈使用rownum的一个注意点。

 

 

简单的说，rownum是在使用select查询的时候，自动生成的一串顺序号。在每返回一条数据结果的时候，rownum就自动加一。rownum可以使用在select后的列表中，也可以使用在where条件后。Oracle中没有top n命令，通常都是使用rownum来控制返回数据的条数。

 

 

SQL> select count(*) from emp;

 

  COUNT(*)

----------

        14

 

SQL> select * from emp where rownum<3; //使用rownum来限制条数

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7369 SMITH      CLERK      7902 1980-12-17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30

 

 

rownum与>符号

 

我们大都知道一个原则，就是rownum在where后面的时候，要慎用>符号。当使用rownum>XXX的时候，返回结果是错误的。

 

 

SQL> select * from emp where rownum>3;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 

 

明明数据表中有数据，但是使用rownum>3的条件时，却是一条也无法搜到。刚刚接触的时候，觉得很诡异。让我们一起看看执行计划。

 

 

SQL> explain plan for select * from emp where rownum>3;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

------------------------------------------

Plan hash value: 2063368778

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  COUNT              |      |       |       |            |          |

|*  2 |   FILTER            |      |       |       |            |          |

|   3 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM>3)

 

15 rows selected

 

 

注意，Oracle生成的执行计划比较简单。对EMP进行全表扫描，将每条记录进行筛选（filter操作），符合条件的记录才会最后返回给前台。筛选filter的条件就是rownum>3。

 

解释这个现象，还要从rownum的原理入手。rownum并不是一列事先写好的数值，而是随着select的结果集而动态推进的一个数据序列。rownum初始为1，有一条返回的时候，rownum推进数1。解释刚刚的rownum>3，开始有一条数据返回的时候，rownum为1。但是应用filter条件的时候，因为没有满足rownum>1的条件，所以该行数据被筛去，rownum没有被推进1。这时候第二条结果来，同样不会推进rownum，这样就是一个数据结果都没有。

 

根据这个思路，我们可以让rownum>XX起效果。

 

 

SQL> select count(*) from emp where rownum>0;

//出现结果集合

  COUNT(*)

----------

        14

 

当rownum为1初始值的时候，满足了filter的条件，所以能够顺利推进rownum取值。

 

SQL> explain plan for select * from emp where rownum>0;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2063368778

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |    14 |   518 |     3   (0)| 00:00:01 |

|   1 |  COUNT              |      |       |       |            |          |

|*  2 |   FILTER            |      |       |       |            |          |

|   3 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM>0)

 

15 rows selected

 

 

 

 

rownum与order by

 

我们回到本篇的正题，关于rownum与order by。我们有时候遇到这样的需求：“列出按XXX从高到低（逆序）的前五行”。排序+控制显示行数，就是order by和rownum结合的场景。

 

首先，我们构建一下实验环境。

 

 

SQL> conn scott/tiger@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL> create table t as select rownum as row_num, object_name from user_objects;

 

Table created

 

SQL> select * from t;

 

   ROW_NUM OBJECT_NAME

---------- --------------------------------------------------------------------------------

         1 PK_DEPT

         2 DEPT

（篇幅原因，省略部分内容…）

        20 SOU

        21 TAR

 

21 rows selected

 

//收集统计量

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

 

首先，我们实验一条SQL，也是笔者当年的一种写法（惭愧ing…）。

 

 

SQL> col object_name format a20;

SQL> select * from t where rownum<5 order by row_num desc;

 

   ROW_NUM OBJECT_NAME

---------- --------------------

         4 PK_EMP

         3 EMP

         2 DEPT

         1 PK_DEPT

 

 

结果显然是不正确的！排序是做到了，但是是在找到的几个数据中进行的排序。我们看看执行计划。

 

 

SQL> explain plan for select * from t where rownum<5 order by row_num desc;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 846588679

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |     4 |    52 |     3   (0)| 00:00:01 |

|   1 |  SORT ORDER BY      |      |     4 |    52 |     3   (0)| 00:00:01 |

|*  2 |   COUNT STOPKEY     |      |       |       |            |          |

|   3 |    TABLE ACCESS FULL| T    |    21 |   273 |     3   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM<5)

 

15 rows selected

 

 

这里，我们看到了一个操作“COUNT STOPKEY”，应该是由于rownum而产生的。根据Oracle官方文档的解释，如下：

 

“Count operation where the number of rows returned is limited by

the ROWNUM expression in the WHERE clause.”

 

含义是，这是一个计数操作。当在where子句中使用rownum的时候，会引入这个操作。这个操作的作用就是限制返回的行数，当返回数量达到rownum的限制（filter）的时候，就结束操作，不会去管剩下的数据。

 

这样的解释就对了，我们的数据表T里面虽然需要进行全排序才能确定最大的四个row_num行，但是当前SQL的执行计划是“选取四行，在排序”。这样显然不是我们的需要了。

 

那么，如何在不改变SQL结构的情况下，获取到正确的结果呢？ 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-687124/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-687124/

ciqu9915
关注
Oracle查询中rownum与Order by查询的关系（取数据的前几条）
11-29 3638
在开发应用的时候，把数据按照一定的规则排序后再取前几条数据这种操作是很平常的事情。我们在Oracle中常用的就 是order by，然后取得rownum小于多少的数据这种方法。不过如果对Oracle不熟悉，也许就会发现你写的SQL语句检索 出来的值不正确，这个是为什么呢. 因为Oracle在检索的时候，会首先把数据都检索出来，然后在排序段中进行排序(也就是说，先有rownum值，然后才
rownum和order by顺序问题
WELL_HUHU的博客
11-01 2442
执行顺序：先执行rownum，再执行order by。 select 列名（s） from 表名 where 条件 and rownum &lt; n order by 列名;   查询排列顺序（order by）后的前n条记录 解决思路：从排列好的列表里查询前n条 select * from (select 列名（s） from 表名 where 条件 order by 列名) w...
Rownum与Order by
haiross的专栏
01-26 3068
Rownum与Order by rownum是数据表的一个虚列，是随着select获取到的数据集合而依次生成的数据项目。借助rownum，很多复杂的SQL需求都得以解决。但是，rownum绝不是一个数据列，使用它的时候一定要注意rownum的生成原理和特性。本篇就从rownum与order by结合，谈使用rownum的一个注意点。     简单的说，rownum是在使用selec
Oracle中的rownum与Order By之间的关系
iteye_19319的博客
06-22 216
    刚学ORACLE的时候，就先入为主地认为rownum是一种类似于SQLSERVER的TOP关键字一样的机制————起码咱们最常用的分页可以用这个搞定。    事实并非如此，学习的时候，“空”是很重要的，以前的经验往往是一种坏处。    SQLSERVER中，如果TOP关键字和ORDER BY关键字一起出现，是先ORDER BY 后再TOP。也就是说：取得最前面的几条记录是经过排序的。   ...
rownum与order by 子句
萝卜&青菜的博客
09-11 1185
一、 rownum的特点 rownum的特点是在select语句查询过程中获得一条符合条件的数据行放入结果集合中时，oracle系统就会自动编一个号，即一个rownum值。这里要强调一点的是，一个结果集合里的任何时刻，都是有一个rownum值为1的数据行的，其后的数据行编号依次为2，3，。。。。以此类推的。如果那一条rownum值为1的数据行从结果集合里剔除了，跟在其后为2的数据行自...
rownum order by
weixin_34194379的博客
04-01 56
用 rownum 一定要用order by order by 要保证唯一性 转载于:https://www.cnblogs.com/sunxiangfu/p/4261049.html
Oracle查询中rownum与Order by查询的关系
06-25 1524
        在开发应用的时候，把数据按照一定的规则排序后再取前几条数据这种操作是很平常的事情。我们在Oracle中常用的就是order by，然后取得rownum小于多少的数据这种方法。不过如果对Oracle不熟悉，也许就会发现你写的SQL语句检索出来的值不正确，这个是为什么呢。        因为Oracle在检索的时候，会首先把数据都检索出来，然后在排序段中进行排序。假如你有一个SQL
orcale 在select后加子查询，如何在子查询种使用rownum和order by
05-23
以下是一个使用 ROWNUM 和 ORDER BY 子句的示例： ``` SELECT * FROM ( SELECT * FROM table_name WHERE condition ORDER BY column_name ) WHERE ROWNUM ; ``` 在这个示例中，主查询使用 ROWNUM 进行限制，...
orcale 在select后加子查询，如何在子查询种使用rownum和order by，请举一个例子
05-23
可以在子查询中使用ROWNUM和ORDER BY，具体可以按照以下语法来编写查询语句： ...注意，在子查询中使用ROWNUM和ORDER BY时，需要将子查询嵌套在一个外层查询中，以便在外层查询中使用ROWNUM来对结果集进行排列和筛选。
oracle加了rownum后就很慢,rownum orderby非常慢
05-30
在 Oracle 数据库中，使用 ROWNUM 和 ORDER BY 子句时可能会导致查询性能下降。 这是因为在使用 ROWNUM 和 ORDER BY 子句时，Oracle 必须将查询结果集全部返回到应用程序或客户端，然后再进行排序和筛选操作，这...
SQL 通过sum() over(order by ,rownum)实现财务现金日记账实例
qq_41451794的博客
12-22 759
SELECT 类型,to_char(发生日期,'yyyy-MM-dd') 发生日期,期初余额,借方金额,贷方金额, (CASE WHEN 借方金额>0 THEN '借' WHEN 贷方金额>0 THEN '贷' ELSE '平' END) 方向, sum(收支金额) over(order by 发生日期, rownum) 余额 FROM( SELECT LX 类型,账户,账号,摘要,发生日期,单据ID, ...
oracle rownum orderby
happy 露
08-27 623
在EMPNO上有索引，ENAME没有索引 (1)SELECT ROWNUM,A.* FROM EMP A ORDER BY ENAME 执行结果： ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 11 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 2 7499 ALLEN
rownum和order by 以及index的关系
daimin's BLOG
08-28 3761
 本篇文章主要讨论rownum和排序以及索引的关系问题1、rownum正序是指什么？问题2、rownum和order by语句在一起时，执行的先后顺序是什么？order by语句如何影响rownum正序？问题3、rownnum与索引之间的关系？-- Create tablecreate table DAIMIN( ID       NUMBER not null,  PARENTID NUMB
oracle的rownum顺序,ORACLE数据库中的ROWNUM和ORDER BY执行顺序
weixin_39604350的博客
04-09 539
使用 SQL 查询 ORACLE 表数据的时候，可能会有如下两种结果需求。 对查询结果集排序，并获得其排序前的行号 对结果集排序后，为每一行加入行号 对于上述两种结果需求，编写 SQL 语句的时候，需要注意 ROWNUM 赋和 ORDER BY 的执行顺序。如果 ORDERBY 的基准使用SQL查询ORACLE表数据的时候，可能会有如下两种结果需求。对查询结果集排序，并获得其排序前的行号对结果...
order by与rownum
05-18 107
--如果的记录无序SQL> select * from t_mid; A-------------------------------------...
Oracle中Order by和rownum执行先后顺序的问题
热门推荐
jzzw的博客
05-14 1万+
今天无意间看了一个文章，文章里这个面试官说他在面试的时候一般都会问两个问题： 1.order by和rownum执行顺序的问题 2.union和union all的区别 union和union all的区别这个我知道，区别在于去不去重(这个地方有问题，下面说)，order by和rownum执行顺序的问题这个我还真不知道，所以说想研究一下 网上查了一下。比较靠谱的说法是order by涉及到...
order by 和 rownum = 1连用，得不到想要的结果
qq_37375667的博客
12-24 1495
select t.CREATE_TIME from table_name t where rownum = 1 order by t.CREATE_TIME desc; 正确的写法： select * from (select t.* from LMS_PROCESS_CHECK t order by t.CREATE_TIME desc) where rownum = 1;
SQL中rownum和order by的执行顺序的问题
极客神殿
04-18 7239
在一个SQL中，如果同时使用rownum和order by，会有一个先后顺序的问题。比如select id1,id2 from t_tablename where rownum<3 order by c_date desc ，这个语句会先从结果集中取前三条记录，再对这3天记录按日期排序。如果设计者向先对日期排序，再取前三天记录，那么就不会得到正确的结果。所以rownum和order by一起使用的时
rownum与order by 子句的执行顺序关系
zhouxzcsdn的博客
03-01 2345
如果select语句（非嵌套查询形式的）有order by子句，则order by子句一般都是最后一步执行的。但是如果order by子句里的字段被设置了主键约束或是被设置索引了，那么order by子句执行之后，oracle系统还会重新对结果集的rownum值进行编号。也就是说，order by子句里的字段被设置了主键约束（或是被设置索引）的select语句，先是在数据行选入结果集时对其进行一次编
oracle的order by和rownum
最新发布
06-28
Oracle的order by是用来对查询结果进行排序的，可以按照一个或多个列进行排序，可以指定升序或降序排列。例如： SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC; 其中，column1是按照升序排列，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值