Oracle-Top-N分析

Top-N，根据某一规则进行排序，然后取其前N行数据。

rownum：伪列，数据表中本身没有这样的列，是oracle数据库为每个数据表加上的列，可以标识别行号，默认情况下，rownum按主索引来排序，若没有主索引则自然排序。

对oracle进行分页必须使用rownum：

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

---

(1)未进行规则排序时，表数据显示

select rownum, employee_id ,last_name,salary
from employees 

---

(2)根据salary进行降序排列

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

可以发现，rownum (伪列)与employee_id存在一一对应关系。

---

(3)现在取salary排名前二十的数据
[由上图可知，前二十的salary在9600以上]

那么这样写对不对？

select rownum rn, employee_id ,last_name,salary
from employees 
where rownum <= 20
order 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,salary
from 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,salary
from 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,salary
from 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,salary
from employees 
order by salary desc
)
where rownum <= 20 and rownum >10

不好意思，无数据。!!!
【rownum 只可使用 < <=，不可使用> >=】

---

解决思路：将此时查出的数据作为基表，再进行查旬，将伪列 rn2 作为新表中的实列，即可进行 < 、<=、>、>=操作。

select rn2,employee_id,last_name,salary
from(
select rownum rn2,rn1, employee_id,last_name,salary 
from (select rownum rn1, employee_id ,last_name,salary
from 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,salary
from(
select rownum rn2,rn1, employee_id,last_name,salary 
from (select rownum rn1, employee_id ,last_name,salary
from employees 
order by salary desc)
)
where rn2 <= 20 and rn2 >10