`

Oracle TOPN分析及 rownum相关知识

阅读更多

ORACLE中经常会进行TOP N的查询,即列出按照一定排序的数据记录。这种查询操作分两种:


1.排序,列出所有记录或前N条记录


2.排序,列出指定区间的记录

 

在ORACLE中是通过对表中的一个伪列 rownum进行操作的。也就是因为这一点,使得在ORACLE中进行TOP N的查询,尤其是指定区间的排序查询比较复杂和难以理解。

 

现在我们先看看第一种,根据第一种的定义我们很容易写出以下语句:

 

select 字段列表 from
  (select 字段列表 from 表名 order by 排序字段)
where rownum<=5

 

以scott账户为例,要求:查出emp表中以工资降序的前5名员工的所有信息,查询语句如下:

 

select * from
  (select * from emp order by sal desc)
where rownum<=5

 

有的朋友可能笑了,这还用得着子查询?我写以下语句不也能搞定吗?

 

a.select * from emp where rownum<=5 order by sal desc

b.select * from emp order by sal desc  where rownum<=5

 

以上两种查询语句都是不对的,先说b,大家要明白ORACLE的查询语句也有其固定语法的,如下:

 

select 字段列表 from 表名 +where 子句  +group by 子句  +having 子句  +order by 子句,所以b不符合语法直接否定,这样的语句运行后会报语句没有正确结束的错误。

 

而a查询语句执行后,返回的结果并不是我们预期的。这是因为虽然a查询语句虽然符合语法规则,但是逻辑上却不合理。它的逻辑意义是先取出前五条记录然后再排序,显然将我们所要做的TOP N分析的步骤搞反了。

 

接下来我们继续看看第二种情况,有的朋友可能会从第一种情况中推理出以下语句:

 

select 字段列表 from
  (select 字段列表 from 表名 order by 排序字段)
where rownum>=11 and rownum<=15

 

毫无疑问,这句查询语句执行后不会有任何结果。要讲明白此查询语句的错误,我们就不得不把rownum拿出来研究研究了。

 

首先大家要知道rownum是ORACLE在我们查询时自动生成的一个从1开始计数的伪列(就是一个虚假的列,看起来不存在,但是却实实在在存在,呵呵。。。比较难理解吧?要不我怎么说因为这点所以才造成ORACLE TOP N查询的困难呢?大家继续往下看,会慢慢理解的。我也是花了很多时间去理解的。)

 

这个伪列的产生机理是这样:当我们进行查询操作时,数据库的记录一条一条拿出,并给词条记录自动生成伪列rownum,这里我再强调一下:rownum是从1开始计数的。

 

那么让我们回头看看上边的查询语句为什么会什么都没有查到。当执行了查询操作后,数据库先拿出一条和查询条件比较。这里的查询条件有两个:rownum>=11 和rownum<=15。查询出的rownum是从1开始计数的,也就是拿出的记录rownum=1.它满足rownum<=15但是不满足rownum>=11。所以被无情的抛弃了。紧接着再拿出一条数据,当然这条数据还会走刚才那条数据的老路:自己的rownum的值被赋予了1,然后与rownum>=11 和rownum<=15的条件比较,结果当然还是被抛弃。这个时候大家也许就会恍然大悟了,如果数据库是这样运行的。那么就永远不会第一次取出rownum>=2的值了,更不用谈>=15了。所以以上的查询语句就不会有查询结果。

 

要实现TOP N的查询第二种的实现我们不得不发挥我们的聪明才智了。于是有了以下的查询语句:

 

select * from(select rownum myno,a.* from (select * from emp order by sal desc) a) b where myno>=5 and myno<=10;

 

以上的语句由内到外划分可分为3部分:

1.select * from emp order by sal desc

   这条查询语句是为其查询结果排序的,这个相当容易理解,再次不多讲解。

 

2.select rownum myno,a.* from (select * from emp order by sal desc) a

   这条查询语句将第一条查询语句作为子查询,可以将第一条语句的查询结果作为一个临时表,并且别名为a。这张临时表中的记录与原来的emp表相同,只是全部排序过。

   本查询语句的查询结果就是 rownum myno   和 a.*.

   rownum myno 就是每条记录系统自动给赋予的rownum,读到这里有的朋友会问:为什么要给他起个别名呢?  答案是:不得不起,不然后面的业务没法开展。而且我们之所以能实现TOP N的查询第二种的实现最大的功臣就是这个别名了,继续往下看你就会明白。

   a.*就好理解了,指的就是a表的所有内容

 

3.select * from(select rownum myno,a.* from (select * from emp order by sal desc) a)  b where myno>=5 and myno<=10;

这条语句就是把第二条的查询结果作为子查询,作为一张临时表进行操作,别名为b.
b表中不仅有排序好的emp表的所有记录,此时它还多了一列实体列myno.通过对myno的操作进行查询就没有什么问题了。因为它是本来就存在的,并不是系统在查询时才临时生成的。你可以把myno理解为b表的固有列。如果在第三次查询时你用的是rowno而不是myno进行操作,那么你还会进入系统临时产生rowno的错误当中。

 

总结一下其核心思想:就是将伪列rowno想办法实例为一个可操作的固有列,通过这个固有列来达到TOP N分析的第二种实现。

分享到:
评论

相关推荐

    sql语句中select top n与oracle的rownum与mysql的limit用法

    sql语句中select top n与oracle的rownum与mysql的limit 取前几条数据sql简单用法

    如何在Oracle中实现SELECT TOP N的方法

    在Oracle数据库中,由于不直接支持SQL Server中...总之,Oracle中实现`SELECT TOP N`的效果主要依赖于`ORDER BY`和`ROWNUM`的组合,通过这种方式,用户可以根据需求灵活地获取数据集的前N条、第N条或N条范围内的记录。

    ROWNUM的使用技巧

    因为 ROWNUM 是在结果集返回之前进行排序的,因此,使用 ROWNUM 对排序结果进行 Top N 取值,可能不会达到预期的结果。 解决方案 为了避免这些问题,我们可以使用子查询和 ROWNUM 结合来实现 Top N 结果输出。例如...

    如何在Oracle中实现SELECT_TOP_N的方法

    Oracle中通常使用`ROWNUM`和`ORDER BY`结合的方式实现`SELECT TOP N`的功能。首先对数据进行排序,然后通过`ROWNUM`限制返回的行数。例如,如果我们有一个名为`CUSTOMER`的表,想按`NAME`字段的字母顺序获取前三个...

    解析oracle的rownum

    如果我们想要找到从第二行记录以后的记录,当使用 ROWNUM&gt;2 是查不出记录的,原因是由于 ROWNUM 是一个总是从 1 开始的伪列,Oracle 认为 ROWNUM&gt; n(n&gt;1 的自然数)这种条件依旧不成立,所以查不到记录。 ```sql SQL...

    Oracle 的top问题

    在Oracle数据库中,"Top N"问题通常指的是查询数据集中最大的N个记录,这在报表、数据分析或数据展示等场景中非常常见。由于Oracle数据库不直接支持像SQL Server中的`TOP`子句来实现这个功能,因此需要采用一些特定...

    在ORACLE中实现SELECT TOP N的方法

    在Oracle中实现SELECT TOP N的方法主要依赖于`ROWNUM`伪列。通过合理的子查询结构,可以轻松实现获取指定数量的记录或从特定行号开始的连续记录等功能。理解并熟练掌握这些基本方法,对于处理各种实际应用场景中的...

    oracle rownum 使用技术.pdf

    然而,在对结果进行排序后使用ROWNUM获取Top N数据时,需要注意一些潜在的问题。这是因为ROWNUM在执行计划的早期阶段就被计算,而ORDER BY子句是在后期阶段才被处理。这意味着即使对数据进行了排序,ROWNUM仍然可能...

    oracle_SQL中ROWID与ROWNUM的使用

    #### ROWNUM 的使用——TOP-N 分析 在 Oracle SQL 中,`ROWNUM` 是一种特殊的伪列,用于标识查询结果集中的行编号。它常用于实现 TOP-N 查询,即返回结果集中满足特定条件的前 N 条记录。 **特点:** - `ROWNUM` ...

    Oracle中ROWNUM的使用技巧.docx

    5. **其他方法**:除了ROWNUM,还可以考虑使用Oracle的其他高级查询功能,如`FETCH FIRST N ROWS ONLY`(需要Oracle 12c及以上版本),它可以直接用于实现分页,同时保持排序的正确性。 总之,ROWNUM在Oracle中是一...

    oracle select top的方法

    本文将详细介绍如何在Oracle中实现类似“Select Top”的功能,并解决相关的需求。 #### 二、基础概念理解 ##### 1. Rownum - **定义**: `ROWNUM` 是一个伪列,用于返回行的序号,其值从1开始,按查询结果的物理...

    在ORACLE中SELECT TOP N的实现方法

    在Oracle中,可以通过结合`ORDER BY`和`ROWNUM`来实现类似`SELECT TOP N`的功能。基本的查询结构如下: ```sql SELECT 列名1, 列名2, ..., 列名n FROM ( SELECT 列名1, 列名2, ..., 列名n FROM 表名 ORDER BY ...

    oracle高级应用基础.pdf

    例如,可以使用`ROWNUM`来选择结果集中的前N行,或者在结合子查询和外部查询的情况下实现TOP N查询。 此外,文档中还涉及了一些错误的查询语句,可能是由于OCR扫描错误导致的。比如`selectrownum,id,age,...

    java笔记\Oracle里sql不能用limit的处理

    在Oracle中,我们可以使用RowNum函数、Minus运算符、Subquery和Top-N查询来实现分页查询。这些方法可以根据不同的需求选择合适的方法来实现分页查询。在开发中,我们需要根据实际情况选择合适的方法来实现分页查询。

    Oracle 9i与MS SQL Server 2000之比较

    比如在`SELECT`语句中,Oracle支持`SELECT * FROM table WHERE ROWNUM &lt;= N`来实现类似`TOP N`的效果,而在SQL Server中,你需要使用`SELECT TOP N * FROM table`。`INSERT`语句的语法也略有不同,Oracle允许一次...

    SqlServer转换Oracle.docx

    - **TOP子句**:SQL Server中的`TOP`子句在Oracle中可以使用`WHERE ROWNUM &lt;= N`替代。 - **GUID生成**:SQL Server中的`NEWID()`函数在Oracle中可以通过自定义函数实现,例如文档中提供的`NEWID`函数示例。 #### ...

    SqlServer与Oracle差异

    - Oracle使用`rownum`,如`SELECT * FROM table_name WHERE rownum &lt; n`。 - Sql server使用`TOP`,如`SELECT TOP n * FROM table_name`。 - 查看表的结构: - Oracle可以使用`DESC table_name`或查询`user_tab_...

Global site tag (gtag.js) - Google Analytics