查询所有班级中每个部门薪水前5名的员工姓名，应该怎样写查询语句？

第一次遇到这个问题还是在JDZ的时候，省支撑的XY(kevin，我校友)提供的答案，非常好很精练。

后来在ITPUB上见到有人问同样的问题，也见到一些不错的答案。

我知道的方法一共有这么三种：

[@more@]

是根据 hr 用户下的 employees 写的，实现的功能类似

代码:

---------------------------------------------------------------------select department_id, last_name, salary, rnfrom (select e.last_name, e.salary, e.department_id, rank() over(partition by e.department_id order by e.salary) rnfrom employees e where e.department_id > '0') where rn <= 5;-----------------------------------------------------------------

没用分析函数写了一个------------------------------------------------------------------select b.department_id, b.salary, b.last_namefrom (select department_id, min(rn) mrnfrom (select department_id, salary, last_name, rownum rnfrom (select e.department_id, e.salary, e.last_namefrom employees ewhere e.department_id > '0'order by e.department_id, e.salary))group by department_id) a,(select department_id, salary, last_name, rownum rnfrom (select e.department_id, e.salary, e.last_namefrom employees ewhere e.department_id > '0'order by e.department_id, e.salary)) bwhere a.department_id = b.department_idand b.rn >= a.mrnand b.rn <= a.mrn + 4;

--------------------------------------------------------------------------

kevin的答案：

select a.department_id,a.salaryfrom employees awhere not exists (select 1 from employees b where a.department_id=b.department_idand a.salaryhaving count(*)>=5)order by a.department_id,a.salary

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/219138/viewspace-913375/，如需转载，请注明出处，否则将追究法律责任。