Question

emp表中查询每个部门工资前三的员工信息

select * from
(select rank() over (partition by deptno
order by sal desc) rk ,emp.*
from emp)
where rk<=3;
但结果带有重复项，如何利用关键字distinct排除重复项？

Answer 1

你那不叫重复，用rank的时候工资一样的话，会并列第一。用row_number
select *
from (select row_number() over(partition by deptno order by sal desc) rk, emp.*
from scott.emp)
where rk <= 3;这样就可以解决。

追问

试了下你说的 还是有重复的 我要的结果是这样的
rk 工资 部门
1 3000 20
1 3000 20
2 2800 20
3 2500 20

追答

select rk, sal, deptno
from (select dense_rank() over(partition by t1.deptno order by t1.sums desc) rk,t.sal,t.deptno,t.empno
from scott.emp t,
(select deptno,empno,sum(sal) over(partition by deptno,empno) sums from scott.emp ) t1
where t.deptno=t1.deptno
and t.empno=t1.empno )
where rk <= 3;