oracle sql
rownum
在查询中,我们可以注意到,类似于“select xx from 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代码
selecttop3 e_namefromptemp.cuishen_temp_20100707orderbyid
select top 3 e_name from ptemp.cuishen_temp_20100707 order by
id
Sql代码
selecttop3 *fromptemp.cuishen_temp_20100707orderbyid
select top 3 * from
ptemp.cuishen_temp_20100707 order by id
2. 用rank给记录排名
默认是降序排名
Sql代码
selectrank(e_name), id, e_namefromptemp.cuishen_temp_20100707_2qualify rank(e_name) <> 2
select rank(e_name), id, e_name from
ptemp.cuishen_temp_20100707_2 qualify rank(e_name)
<> 2
也可以指定asc关键字进行升序排名
Sql代码
selectrank(e_nameasc), id, e_namefromptemp.cuishen_temp_20100707_2qualify rank(e_nameasc) <= 2
select rank(e_name asc), id, e_name from
ptemp.cuishen_temp_20100707_2 qualify rank(e_name asc)
<= 2
其中
Sql代码
qualify rank(e_nameasc) <= 2
qualify rank(e_name asc) <=
2
子句表示对结果集进行限制,选取e_name字段升序排名前2的记录
限定条件当然也可以这样写:
Sql代码
selectrank(nameasc), id,namefromptemp.cuishen_temp_20100707qualify rank(nameasc) >= 2andrank(nameasc) <= 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*fromptemp.cuishen_temp_20100707qualify row_number() over(orderbyid) >= 2androw_number() over(orderbyid) <= 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代码
select*fromptemp.cuishen_temp_20100707QUALIFY ROW_NUMBER() OVER(PARTITIONBYidORDERBYid) = 1
select * from ptemp.cuishen_temp_20100707
QUALIFY ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) = 1
上句表示:按id字段排序,取每段重复id记录的TOP 1。千万注意:这个不能用rank来做。