select deptno, ename, sal
from emp e1
where
(
select count(1)
from emp e2
where e2.deptno=e1.deptno and e2.sal>=e1.sal
) <=3 /*这里的数值表示你想取前几名*/
order by deptno, sal desc;
2,方法二
-- row_number 生成的行连续号,没有重复的
select t.*
from (select row_number() over(PARTITION by deptno order by sal desc) rn,e.* from emp e) t
where t.rn <= 3;
3,方法三
---dence_rank在并列关系时,相同等级不会跳过。rank则跳过
select t.*
from (select rank() over(PARTITION by deptno order by sal desc) rn,e.* from emp e) t
where t.rn <= 3;
4,方法四
select t.*
from (select dense_rank() over(PARTITION by deptno order by sal desc) rn,e.* from emp e) t
where t.rn <= 3;