rownum偽劣的相關(guān)問題
?
?
1、在SQL中使用rownum的限制：
?
??? select
*
from
t1
where
rownum
>=
1
;
??? select
*
from
t1
where
rownum
>=
2
;
???????????對于上面兩個SQL來說，第一個可以返回所有記錄，第2個SQL返回0條記錄；
?????????? 原因是Oracle掃描第1條記錄時，因為不滿足而無法輸出，因此rownum序列重新置0，然后永遠因無法滿足條件而不能輸出，最終返回0條記錄。包括rownum=2也由于此原因不能得到記錄。
?
?????????? 所以當(dāng)需要rownum >= n 時，需要先把rownum轉(zhuǎn)化為實列，例如：
??? select
num
from
??? (
select
num,
rownum? rn fromt1)
??? where
rn >=
10
;
?
?
??? 另外，rownum列不能加任何基表的前綴，例如：
??? select id,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
;
?
?????注意：千萬不能使用以下方法
??? select * from
??? (select num from t1 order by id)
??? where rownum <= 10
??? minus
??? select * from
??? (select num from t1 order by id)
??? where rownum <= 4 ; ----會發(fā)生錯誤，因為minus自動distinct
?
?
3、order? by的一些特性：
?
??? (select * from t1);
??? select * from t1 order by id;
??? (select * from t1 order by id);--發(fā)生錯誤ORA-00907: missing right parenthesis
??? (select * from t1 )order by id;
??? select * from (select * from t1 order by id);
?????其中除第3個外，其余均可正確執(zhí)行，主要是由于Oracle的解析規(guī)則造成，修改成第4種方式即可。
?
?
4、一條關(guān)于rownum語句的問題：
?
??? select * from t1 where rownum <= 10 order by id desc;
??? select * from t1 where rownum <= 10 order by num desc;
?????比較上面兩條語句，唯一的區(qū)別是id字段上有索引，num字段沒有索引
?
????乍一看這兩條語句都會先進行篩選rownum <= 10 然后再排序，則得出的結(jié)果集應(yīng)該是相同的10行記錄
????但是實際上兩條語句運行出來的結(jié)果是完全不同的
????主要的原因還是由于RBO會判斷排序字段是否有索引，如果存在索引，就先排序，然后進行rownum篩選，如果字段無索引則先進行篩選再排序。
?
?
5、標準SQL函數(shù)row_number() over()的用法：
?
??? select id,num,row_number() over(partition by id order by num) from t1;
????即對id分組后，再按照num的排列順序取出一次的row_number
????在實際應(yīng)用中可以取出例如：每個班的最后10名的成績，每個月最大的20比交易等等。
?
?
6、使用rownum提取依次的序列：
?
???? 這是一個比較基本的應(yīng)用了：
??? select rownum from dual connect by rownum<=10;
?????????? 把rownum換成level的效果也是一樣：??? select level from dual connect by level<=10;
?
?????這個在實際的應(yīng)用還是比較廣泛的，例如：列出當(dāng)月的所有天數(shù)
??? select trunc(sysdate,'mm')+rownum-1 as everyday from dual
??? connect by rownum <= to_number(to_char(last_day(sysdate),'dd'));
?
?
-The End-