一个经典的查询及其SQL语句

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

第一次遇到这个问题还是在JDZ的时候，省支撑的XY(kevin，我校友)提供的答案，非常好很精练。
后来在ITPUB上见到有人问同样的问题，也见到一些不错的答案。

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

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

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

没用分析函数写了一个
------------------------------------------------------------------
select b.department_id, b.salary, b.last_name
from (select department_id, min(rn) mrn
from (select department_id, salary, last_name, rownum rn
from (select e.department_id, e.salary, e.last_name
from employees e
where e.department_id > '0'
order by e.department_id, e.salary))
group by department_id) a,
(select department_id, salary, last_name, rownum rn
from (select e.department_id, e.salary, e.last_name
from employees e
where e.department_id > '0'
order by e.department_id, e.salary)) b
where a.department_id = b.department_id
and b.rn >= a.mrn
and b.rn <= a.mrn + 4;

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

kevin的答案：
select a.department_id,a.salary
from employees a
where not exists
(select 1 from employees b
where a.department_id=b.department_id
and a.salaryhaving count(*)>=5)
order by a.department_id,a.salary

转自：http://abcbbc.itpub.net/post/31127/285480

[@more@]

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

转载于:http://blog.itpub.net/7911270/viewspace-913378/