Oracle-Top-N分析____Oracle

Top-N，根據某一規則進行排序，然後取其前N行資料。

rownum：偽列，資料表中本身沒有這樣的列，是oracle資料庫為每個資料表加上的列，可以標識別行號，預設情況下，rownum按主索引來排序，若沒有主索引則自然排序。

對oracle進行分頁必須使用rownum：

select employee_id,last_name,salaryfrom (select rownum rn,employee_id,last_name,salary from employees)ewhere e.rn <=pageNo*pageSize and e.rn>(pageNo-1)*pageSize

(1)未進行規則排序時，表資料顯示

select rownum, employee_id ,last_name,salaryfrom employees 

(2)根據salary進行降序排列

select rownum, employee_id ,last_name,salaryfrom employees order by salary desc;

可以發現，rownum (偽列)與employee_id存在一一對應關係。

(3)現在取salary排名前二十的資料
[由上圖可知，前二十的salary在9600以上]

那麼這樣寫對不對。

select rownum rn, employee_id ,last_name,salaryfrom employees where rownum <= 20order by salary desc

很顯然，這樣的結果是錯的。。這裡只是選取了表中rownum 前20的資料，然後根據salary 進行了降序排列。

但凡一個表，就存在rownum 偽列。那麼我們可以先進行salary排序，然後以此為基表，再進行尋找。代碼如下：

select rownum rn2,rn1, employee_id,last_name,salary from (select rownum rn1, employee_id ,last_name,salaryfrom employees order by salary desc)--rn1 employees 表的偽列；--rn2 新表的偽列；

此時，rn1、rn2分別與employee_id有對應關係，我們可根據rn2的值取資料。

select rownum rn2,rn1, employee_id,last_name,salary from (select rownum rn1, employee_id ,last_name,salaryfrom employees order by salary desc)where rownum <= 20--where rn2 <= 20 是不對的，where中不能使用外層rownum列的別名--where rn1 <= 20 寫法可以，基表中存在 rn1列

此時取的為salary前20的資料。

有個有意思的現象，如果where rn1<= 20 呢。

select rownum rn2,rn1, employee_id,last_name,salary from (select rownum rn1, employee_id ,last_name,salaryfrom employees order by salary desc)where rn1 <= 20

此時rn2 與employee_id 對應的關係與上圖截然不同。

(4) 取10-20之間的資料

可能會這樣想，修改一下 where就可以了。

select rownum rn2,rn1, employee_id,last_name,salary from (select rownum rn1, employee_id ,last_name,salaryfrom employees order by salary desc)where rownum <= 20 and rownum >10

不好意思，無資料。!!!
【rownum 只可使用 < <=，不可使用> >=】

解決思路：將此時查出的資料作為基表，再進行查旬，將偽列 rn2 作為新表中的實列，即可進行 < 、<=、>、>=操作。

select rn2,employee_id,last_name,salaryfrom(select rownum rn2,rn1, employee_id,last_name,salary from (select rownum rn1, employee_id ,last_name,salaryfrom employees order by salary desc))where rn2 <= 20 and rn2 >10--注意此時的 rn2 ，為中間表的rownum。

此時資料即為(10,20】

對比三個查詢表的 rownum：

select rownum rn3, rn2,rn1,employee_id,last_name,salaryfrom(select rownum rn2,rn1, employee_id,last_name,salary from (select rownum rn1, employee_id ,last_name,salaryfrom employees order by salary desc))where rn2 <= 20 and rn2 >10