[oracle] rownum 及选取排序后的前N条数据

古城微笑少年丶 2022-08-06 06:26 11阅读 0赞

**oracle sql rownum**

在查询中，我们可以注意到，类似于“select xx from [ table][table] where rownum < n”(n>1)这样的查询是有正确含义的，而“select xx from table where rownum = n”这样的查询只在n=1的时候成立，“select xx from table where rownum > n”(n>1)这样的查询只能得到一个空集。另外“select xx from table where rownum > 0”这个查询会返回所有的记录。这是为什么呢?原因就在于Oracle对rownum的处理上，rownum是在得到结果集的时候产生的，用于标记结果集中结果顺序的一个字段，这个字段被称为“伪数列”，也就是事实上不存在的一个数列。它的特点是按顺序标记，而且是逐次递加的，换句话说就是只有有rownum=1的记录，才可能有rownum=2的记录。

让我们回头来分析一下在where中使用rownum作为查询条件的情况。在rownum取=1，或者rownum <= n (n>1)的时候，没有问题。那么为什么当条件为rownum = n或者rownum >= n时明明有数据却只能得到一个空集呢?假设我们的查询条件为rownum = 2，那么在查询出的第一条记录的时候，oracle标记此条记录rownum为1，结果发现和rownum=2的条件不符，于是结果集为空。写到这里，我忽然有一个有趣的想法：假如有一条查询语句为select xx,yy from table where zz > 20 and rownum < 10，那么在执行的时候，是先按照zz>20的条件查询出一个结果集，然后按照rownum取出前10条返回?还是在按照zz>20的条件先查询，然后有一个记录就标记一个rownum，到rownum<10的时候就停止查询?我觉得应该是后者，也就是在执行语句的时候，不是做full scan，而是取够数据就停止查询。要验证这个想法应该很简单，找一个数据量非常大的表进行查询就可以了。可惜目前我没有这样的表。

我们可以看出，直接使用rownum是要受到限制的。但是很容易遇到这样的需求“查出符合条件的第xx条到第xx条记录”，比如页面的分页处理。这个时候如何构造出适合自己的结果集?嗯，墙边那位说全取出来手工挑选的哥们可以拉出去了。当然这样做也是可以的，但是前提是整个数据集的数据条数不多的情况下。假如遇到上十万百条的数据，全部取出来的话，用户就不用干别的事情了。这个时候用户应该怎么做呢?当然就是要用到我们介绍的rownum拉!rownum不是个“伪数列”么，好说，我们现在把它弄成一个实在的字段就可以了。

具体做法就是利用子查询，在构建临时表的时候，把rownum也一起构造进去。比如“select xx,yy from (select xx,yy,rownum as xyz from table where zz >20) where xyz between 10 and 20”这样就可以了。另外使用oracle提供的结果集处理函数minus也可以做到，例如“select xx,yy from table where zz > 20 and rownum <20 minus select xx,yy from table where zz>20 and rownum <10”，但是使用minus好像比使用子查询更加消耗资源。

和rownum相似，oracle还提供了另外一个伪数列：rowid。不过rowid和rownum不同，一般说来每一行数据对应的rowid是固定而且唯一的，在这一行数据存入数据库的时候就确定了。可以利用rowid来查询记录，而且通过rowid查询记录是查询速度最快的查询方法。(这个我没有试过，另外要记住一个长度在18位，而且没有太明显规律的字符串是一个很困难的事情，所以我个人认为利用rowid查询记录的实用性不是很大)rowid只有在表发生移动(比如表空间变化，数据导入/导出以后)，才会发生变化。

**ps.**我在使用的时候必须要select rownum 才能在where中使用rownum ，不知道是为什么......

我的使用例子：

select rownum,t.\* from 表名 t where rownum<=100

rownum 和 distinct

因为 distinct在使用的时候必须放开select内容的开始，如果我还需要rownum ，那么就会有问题：

select distinct name,age,rownum from table where rownum<=100 order by name,age;

这样达不到想要name+age唯一的效果，因为 distinct 是同时作用于name+age+rownum，而rownum本身是不会重复的。

我试了下 select name,age,rownum, count(distinct name,age) from table where rownum<=100 order by name,age;

结果也是不对呢，难道应该是select name,age,rownum, count(distinct name,age)from table where rownum<=100 group by name,age order by name,age; ？？？ 我没有试

我的方法：

set sql "select distinct name,age from table where \*\*\*  order by name,age"

set sql "select rownum, t.\* from ($sql) t where rownum<=100"

**选取排序后的前N条记录**

**1. Sql代码** [ ][Link 1]

**select** **top** 3 e\_name **from** ptemp.cuishen\_temp\_20100707   **order** **by** id

select top 3 e\_name from ptemp.cuishen\_temp\_20100707 order by id

Sql代码 [ ][Link 1]

**select**  **top**  3  \*  **from**  ptemp.cuishen\_temp\_20100707     **order**   **by**   id

select top 3 \* from ptemp.cuishen\_temp\_20100707 order by id  
  
**2. 用rank给记录排名**  
  
默认是降序排名

Sql代码 [ ][Link 1]

**select**  rank(e\_name),  id,  e\_name  **from**  ptemp.cuishen\_temp\_20100707\_2      qualify  rank(e\_name)  <>  2

select rank(e\_name), id, e\_name from ptemp.cuishen\_temp\_20100707\_2 qualify rank(e\_name) <> 2  
  
也可以指定asc关键字进行升序排名

Sql代码 [ ][Link 1]

**select**  rank(e\_name  **asc**),  id,  e\_name  **from**  ptemp.cuishen\_temp\_20100707\_2      qualify  rank(e\_name  **asc** )  <=  2

select rank(e\_name asc), id, e\_name from ptemp.cuishen\_temp\_20100707\_2 qualify rank(e\_name asc) <= 2  
  
其中

Sql代码 [ ][Link 1]

qualify rank(e\_name **asc**) <= 2

qualify rank(e\_name asc) <= 2  
子句表示对结果集进行限制，选取e\_name字段升序排名前2的记录  
  
限定条件当然也可以这样写：

Sql代码 [ ][Link 1]

**select**  rank( **name**  **asc**),  id,  **name**  **from**  ptemp.cuishen\_temp\_20100707      qualify  rank( **name**   **asc** )  >=  2  and   rank( **name**   **asc** )  <=  5

select rank(name asc), id, name from ptemp.cuishen\_temp\_20100707 qualify rank(name asc) >= 2 and rank(name asc) <= 5  
  
**3. 分页查询**  
  
可以用row\_number关键字来进行分页查询，例如：

Sql代码

**select**  \*  **from**  ptemp.cuishen\_temp\_20100707      qualify  row\_number()  over( **order**   **by**   id)  >=  2  and   row\_number()  over( **order**   **by**   id)  <=  5

select \* from ptemp.cuishen\_temp\_20100707 qualify row\_number() over(order by id) >= 2 and row\_number() over(order by id) <= 5  
  
**4. row\_number和rank的区别**  
  
row\_number：顾名思意，就是行号，不管记录相不相同，行号都是不同的。  
rank：对于不同的记录排名当然是不同的，而对于相同的记录排名是相同的，这就是为什么分页查询不用rank来做的原因。  
  
**5. 可以用PARTITION BY关键字进行去重排名查询**

Sql代码 [ ][Link 1]

**select**  \*  **from**  ptemp.cuishen\_temp\_20100707    QUALIFY ROW\_NUMBER() OVER(PARTITION  **BY**   id  **ORDER**   **BY**   id)  =  1

select \* from ptemp.cuishen\_temp\_20100707 QUALIFY ROW\_NUMBER() OVER(PARTITION BY id ORDER BY id) = 1  
上句表示：按id字段排序，取每段重复id记录的TOP 1。千万注意：这个不能用rank来做。

[table]: http://whatis.ctocio.com.cn/searchwhatis/456/6028456.shtml
[Link 1]: http://www.javaeye.com/topic/712465#