查询所有班级中每个部门薪水前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/