Rownum是oracle中的一個偽列,其目的就是給查詢的行標註行號。可以實現查詢前n行,中間幾行,最後幾行的功能(根據業
務功能定義行的排序)。但是rownum又是一個很特殊的列,使用過程中,首先要弄清楚oracle的原理,然後加以分析。
注意點:
1.rownum不可以直接在前面加上表名或別名等。
如 select t.rownum from table t;是錯誤的。
2.rownum和where在同一層查詢中,where條件之後使用rownum比較,只能使用<=,,>=,=(使用=,只能是where
rownum=1才可以)。否則返回null。如果使用!=或<>,那麼只是返回前n-1行,其他按照rownum工作原理推算。
如 select rownum,name from emp where rownum>=5;沒有結果
Select rownum,name from emp where rownum=1;返回第1行
Select rownum,name from emp where rownum !=10;//返回1-9行
3.當rownum和排序在一個語句中使用的時候,要注意,看看oracle有沒有使用索引,如果使用了索引掃描,那麼可能按照索
引來組織資料,如果沒有則是先生成行號,然後order by,則查詢出來的結果亂序。可以採用巢狀查詢,先在內層排序,在
外層查詢rownum。
Rownum原理:
1 Oracle executes your query.
1.執行查詢操作
2 Oracle fetches the first row and calls it row number 1.
2.將第一行的row num置為1
3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then
Oracle return the row.
3.將得到的行的row num與條件相比較,如果不匹配,則拋棄行,如果匹配,則返回行
4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so
forth).
4.oracle獲取下一行,然後將rownum增1
5 Go to step 3.
5.返回第3步
從這個原理可以知道,select rownum,name from emp where rownum>5;不返回行,因為首先執行查詢select name from
emp,將第1行的rownum標為1,然後看where條件,為false,則拋棄行,執行第2行,還是rownum標為1,看where條件還為
false,所以永遠是false,rownum不改變,所有的行都被拋棄,所以沒有結果。
基於以上的問題,那麼使用rownum的常用查詢結構有:
3. 簡單的加上行號
選擇員工,顯示姓名,結果集的行號
select rownum ,last_name from s_emp;
select rownum ,last_name from s_emp where rownum <= 10;
select rownum ,last_name from s_emp where rownum = 1;
下面的不正確
select rownum ,last_name from s_emp where rownum = 10;
select rownum ,last_name from s_emp where rownum >= 10;
4. 獲取前n行
選擇員工,顯示姓名,行號,按照姓名排序
select rownum,last_name from s_emp order by last_name;--不正確
如果要排序,那麼需要在內層查詢中排序好,然後外層查詢引入rownum(兩重巢狀查詢)
select rownum,last_name from
(select last_name from s_emp order by last_name);
選擇前10行,可以直接在兩重巢狀的外層查詢中引入rownum,並且where rownum<=
select rownum,last_name from
(select last_name from s_emp order by last_name)
where rownum<=10;
3.獲取中間行資料
選擇員工姓名,顯示行號,按照姓名排序,顯示第5條記錄到第10條記錄
select rn,last_name from --最外層用where條件判斷
(select rownum rn,last_name --中間層查詢加上行號
from (
select last_name from s_emp order by last_name --底層查詢排序
)
)
where rn between 5 and 10;
也可以在中間層查詢中加入最大的行號判斷,<=,最外層用>=,上面的查詢可以用下面的替換:
select rn,last_name from
(select rownum rn,last_name
from (
select last_name from s_emp order by last_name
)
where rownum<=10 --中間層查詢把要查詢的最大行號最過濾
)
where rn>=5;
4.取最大值的行
查詢出薪資差距最大的部門,顯示名稱
select name
from s_dept
where id = (select dept_id
from (select dept_id, (max(salary) - min(salary)) salary --內層查詢將薪水差計算作為別名,排

from s_emp
group by dept_id
order by salary desc)
where rownum = 1); --外層查詢取得第一行資料
其他的最小,後前行等都可以通