oracle的rowid和rownum簡介

[ 概要 ]

剛剛接觸oracle的同學可能常常會被rowid和rownum這兩個詞弄混, 弄清楚這兩個傢伙對於我們寫sql會有很大的協助, 下面偶就拋磚引玉, 簡單地談談他們之間的區別吧.

[ 比較 ]

rowid和rownum都是oracle中的偽列, 但他們還是存在本質區別:

rowid: 是物理地址, 用於定位元據表中資料的位置, 它是唯一的且不會改變.

rownum: 是根據查詢的結果集給每行分配的一個邏輯編號, 查詢結果不同, rownum自然不同.

對於同一條記錄, 查詢條件不同, rownum會不同, 但是rowid將不變.

樣本: 查詢公司所有的員工

select rowid, rownum, empno, ename from emp;

樣本: 查詢公司員工姓名包含'S'

select rowid, rownum, empno, ename from emp where ename like '%S%';

細心的同學不難發現, 對於一行記錄, rowid沒變, rownum變了. 例如: 姓名為'SMITH'的員工.

[ 陷阱 ]

1. rownum只支援符號<、<=、!=, 不支援>, >=, =, between...and

select * from emp where rownum > 10 -- 沒有結果

select *  from (select rownum row_num, e.* from emp e) emp_temp where emp_temp.row_num > 10; -- 可以擷取十位以後記錄

解釋一下: 因為rownum是對結果集加的一個偽列, 即先查到結果集之後再加上去的一個列(強調: 要有結果集). 簡單地說rownum是符合條件結果的序號, 它總是從1開始排位, 所以你選出的結果不可能沒有1, 而有大於1的值.

有了上面的知識, 我們就不難解釋以下現象:

① 為什麼select * from emp where rownum != 10 總是返回前9條記錄?

因為查詢到結果集後, 顯示完第9條記錄, 之後的記錄都是 != 10.

② 為什麼rownum > 1 查不到一條記錄, 而rownum > 0 或 rownum >= 1 卻總顯示所有記錄?

因為rownum是在查詢到結果集之後加上去的, 它總是從1開始.

③ 為什麼between 1 and 10 或者 between 0 and 10 能查到結果, 而between 2 and 10卻得不到結果?

原因同上, 少了rownum=1就像空中樓閣一樣不能存在.

[ 應用 ]

廢話扯了一籮筐, 這兩哥們到底有啥用呢? 嘿嘿, 別急, 下面就上正菜...

問題: 擷取薪水排名前三的員工

分析: 如果是sql server就好了, 我們可以使用top關鍵字, oracle中我們用rownum也能解決

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

本欄目更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/