Oracel,scott-emp表中，怎么查出每个部门工资前三名的员工信息

用rownumber可以实现这个功能：
select * from (
SELECT a.deptno,(rownum+1-b.min_sno) sno, a.ename,a.sal
FROM
(SELECT *
FROM emp
ORDER BY deptno, sal ) a,
(SELECT deptno, MIN(rownum) min_sno
FROM( SELECT *
FROM emp
ORDER BY deptno, sal desc)
GROUP BY deptno) b
WHERE a.deptno=b.deptno )
where sno <=3

当然，Oracle中有功能更强大的分析函数，很快就能搞定
select tt.empno,tt.ename,b.dname,tt.sal,tt.rn from
(select t.empno,t.ename, t.deptno,t.sal,row_number() over(partition by deptno order by sal desc) rn from emp t)tt,dept b
where b.deptno=tt.deptno and tt.rn<=3;

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;

或
select * from
(select deptno,ename,sal,row_number() over (partition by deptno
order by sal desc) rn
from emp)
where rn<3;