Oracle 之N'st 处理

比如：求emp表中工资第5高的人的姓名和工资

1，用集合操作

select ename,sal from (select * from emp order by sal desc) where Rownum<=5

Minus

select ename,sal from (select * from emp order by sal desc) where Rownum<=4

要进行4此查询，效率低下，不过运用集合的思想，挺有趣。

2，使用分析函数

select ename,sal from

(select ename,sal,row_number() over(order by sal desc) as rank from emp)

where rank=5;

需要两次查询可以获取需要的数值，比较靠谱。

3使用排序

select * 

from(select * from(select ename,sal from emp order by sal desc)where Rownum<=5

order by sal asc

)where Rownum=1;

颠来倒去，看着不怎么舒服，最容易实现的一种方式

4使用游标

declare

n integer;

i integer:=1;

begin

n:=&n;

for temp in (select ename,sal from emp order by sal desc) loop

   if(i=n) then

dbms_output.put_line(temp.ename||' '||temp.sal);

   end if;

   i:=i+1;

end loop;

end;

/

只用一次查询，用游标操作，建议使用。

几个容易犯错的地方

select ename,sal,row_number() over(order by sal desc) as rank

from emp

where rank=5;

这样写会出现rank是没有定义的标识符

如果直接把row_number() over(order by sal desc)写在where子句中又会出现

分析函数不能where子句中运用的错误

select Rownum,ename,sal from emp  where Rownum<=5;

Rownum用来限制从emp表中选择用作处理的行数。

比如

select Rownum,ename,sal from emp  where Rownum<=5 order by sal desc;

这条语句，没有任何问题，但是它的查询结果却并不是从emp表中查询工资前五位

而是从emp表中根据Rownum从oracle的数据文件中选出5条记录(而rownum和物理存储相关联)，然后对其进行排序。

如果想求出工资最高的前五位，须做如下处理

select ename,sal from (select * from emp order by sal desc) where Rownum<=5

有一个比较有意思的东西：

select ename,sal from (select emp.*,Rownum no from emp order by sal desc) where 

no<=5;

此时结果就不是工资的前五位了，而其结果和

select Rownum,ename,sal from emp  where Rownum<=5 order by sal desc;

是完全一样的。

这个很有意思，并且也容易想明白为什么（提示:有两个Rownum )