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