oracle 面试题下 (七) rownum的运用

1 求经理人平均薪水最低的部门名称:

select dname from dept where deptno =
(
select deptno from 
(
select deptno,avg(sal)avg_sal from
(
select e1.sal, e1.deptno,e2.mgr from emp e1 join emp e2 on (e1.empno=e2.mgr)
) group by deptno
) where avg_sal =
(
select min(avg_sal) from
(
select deptno,avg(sal)avg_sal from
(
select e1.sal, e1.deptno,e2.mgr from emp e1 join emp e2 on (e1.empno=e2.mgr)
) group by deptno
)
)
)

2  比普通员工的最高薪水还高的经理人名称

select ename from emp 
where empno in (select distinct mgr from emp where mgr is not null) and sal >
(
select max(sal) from emp where empno not in
    (select distinct mgr from emp where mgr is not null)
)
/

其中(select distinct mgr from emp where mgr is not null)  注意空值的处理.

3 求薪水最高的前五雇员

 * 关于隐藏属性 rownum :

      rownum 从1开始,每行递增 . 如查询表中的前五人:

  select ename from emp where rownum < = 5;

  注意oracle 中 rownum 不可直接与> , = 连用,  如:select ename from emp where rownum >10;

  当需要查找r>10时必须使用子查询:

     select  ename from (select  ename ,rownum r from emp)where r >10;  

  以上.

  求薪水最高的前五人:

    select  ename ,sal from

    ( select ename ,sal from emp order by sal desc )

            where rownum<=5;

4 求薪水最高的第六个人到第十个人

select ename,sal  from
(
select ename ,sal ,rownum r from
(
select ename ,sal  from emp order by sal desc
)
)where r>=6 and r <=10
/

为什么不能  select ename ,sal from

                        (select  ename,sal ,rownum r from emp order by sal desc)

                                     where r >=6 and r <=10   这样写呢?

                     因为 (select  ename,sal ,rownum r from emp order by sal desc) 这句话先取了emp的 rownum 然后再按sal  倒排, 无法达到取 sal 6到10名的效果.