在表emp中,按照sal排序,取出各部门前两名:
方法一:
select * from (
select rank() over(partition by e.deptno order by e.sal desc ) f ,e.empno,e.ename,e.deptno,e.sal
from emp e) e1
where e1.f<=2;
方法二:
select deptno, ename, sal
from emp e1
where
(
select count(1)
from emp e2
where e2.deptno=e1.deptno and e2.sal>=e1.sal
) <=2 /*这里的数值表示你想取前几名*/
order by deptno, sal desc;