一個經典的查詢及其SQL語句

查詢所有班級中每個部門薪水前5名的員工姓名，應該怎樣寫查詢語句？

第一次遇到這個問題還是在JDZ的時候，省支撐的XY(kevin，我校友)提供的答案，非常好很精練。
後來在ITPUB上見到有人問同樣的問題，也見到一些不錯的答案。

我知道的方法一共有這麼三種：

[@more@]

是根據 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