select * from emp where empno in ( select empno from ( select empno from emp order by sal desc )t where rownum<=3 );
select * from ( select * from emp order by sal desc ) where rownum<=3;
oracle 中没有 ms sql中的top n,只能使用rownum伪列或者分析函数(如row_number()等)来处理,分析函数更强大,它可以分组后再排序取指定行 例:如果要取emp中每个部门中工资排前两位的员工,可以使用 select * from (select t.*, row_number() over(partition by t.deptno order by sal desc) rn from emp t) where rn <= 2;
select a.* from ( select t.*, rownum as f_Order from emp t order by t.sal desc )a where a.f_Order <= 3;
貌似会漏掉同为工资第二或者第三的员工,比如:A B C D E 5个员工,A 4000,B C D 3000,E 2000,如果求三名的话,按照上面的方法,只会显示A B C三个人,会漏掉D E两个员工。
select a.ename,a.deptno,a.sal from emp a inner join (select deptno ,max(sal)as sal1 from emp group by deptno )b on a.DEPTNO = b.deptno where sal = sal1
from emp
where empno in (
select empno
from
(
select empno
from emp
order by sal desc
)t
where rownum<=3
);
from
(
select * from
emp
order by sal desc
)
where rownum<=3;
例:如果要取emp中每个部门中工资排前两位的员工,可以使用
select *
from (select t.*,
row_number() over(partition by t.deptno order by sal desc) rn
from emp t)
where rn <= 2;
a.*
from
(
select
t.*,
rownum as f_Order
from emp t
order by t.sal desc
)a
where a.f_Order <= 3;
谢4楼,row_num()很好用啊,又长见识了~~
同谢6楼