select
*
from
t1
where
rownum
>=
1
;
select
*
from
t1
where
rownum
>=
2
;
对于上面两个SQL来说,第一个可以返回所有记录,第2个SQL返回0条记录;
原因是Oracle扫描第1条记录时,因为不满足而无法输出,因此rownum序列重新置0,然后永远因无法满足条件而不能输出,最终返回0条记录。包括rownum=2也由于此原因不能得到记录。
select
num
from
(
select
num,
rownum
rn
from
t1)
where
rn >=
10
;
另外,rownum列不能加任何基表的前缀,例如:
selectid,t1.rownum from t1; --ORA-01747: invalid user.table.column specification
2、对表排序,再取出第i到第j条记录:
select
*
from
(
select
num,
rownum
rn
from
(
select
num
from
t1
order
by
id
))
where
rn
between
5
and
10
;
注意:使用minus来取可能出现问题,因为会自动distinct掉
select * from
(select num from t1 orderbyid)
whererownum <= 10
minus
select * from
(select num from t1 orderbyid)
whererownum <= 4 ; ----会发生错误,因为minus自动distinct
3、order by的一些特性:
(select * from t1);
select * from t1 orderbyid;
(select * from t1 orderbyid);--发生错误ORA-00907: missing right parenthesis
(select * from t1 )orderbyid;
select * from (select * from t1 orderbyid);
其中除第3个外,其余均可正确执行,主要是由于Oracle的解析规则造成,修改成第4种方式即可。
4、一条关于rownum语句的问题:
select * from t1 whererownum <= 10orderbyiddesc;
select * from t1 whererownum <= 10orderby num desc;
比较上面两条语句,唯一的区别是id字段上有索引,num字段没有索引
乍一看这两条语句都会先进行筛选rownum <= 10 然后再排序,则得出的结果集应该是相同的10行记录,但是实际上两条语句运行出来的结果是完全不同的。
主要的原因还是由于RBO会判断排序字段是否有索引。如果存在索引:就先排序,然后进行rownum筛选;如果字段无索引则先进行筛选再排序。
5、标准SQL函数row_number() over()的用法:
selectid,num,row_number() over(partitionbyidorderby num) from t1;
即对id分组后,再按照num的排列顺序取出一次的row_number
在实际应用中可以取出例如:每个班的最后10名的成绩,每个月最大的20比交易等等。
6、使用rownum提取依次的序列:
这是一个比较基本的应用了:
selectrownumfrom dual connectbyrownum<=10;
把rownum换成level的效果也是一样:
selectlevelfrom dual connectbylevel<=10;
这个在实际的应用还是比较广泛的,例如:列出当月的所有天数
select trunc(sysdate,'mm')+rownum-1as everyday from dual
connectbyrownum <= to_number(to_char(last_day(sysdate),'dd'));