| 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); --外層查詢取得第一行資料 其他的最小,後前行等都可以通 |
|