ORACLE 多种方法实现查询

开发工具与关键技术：Oracle sql*plus PLSQL Developer
作者：华境聪
撰写时间：2019年03月29日

本次要说明的题目是：求各个部门的最高工资的最低工资的 员工最低工资
方法一：利用先用order by 后用rownum 截取行数获取
select * from(
select rownum as r,d.department_id,d.sal from(
select a.department_id,min(a.salary) as sal,min(b.sal)
from employees a join (select max(salary) as sal,department_id from employees group by department_id) b
on a.department_id = b.department_id
group by a.department_id
order by min(b.sal)
)d)
where r = 1

方法二：一步一步进行条件的筛选后，表之间的连接查询，最后分组
select a.department_id,min(salary) from employees a join (select min(b.sal) as sal
from (select max(salary) as sal,department_id from employees group by department_id)b)c
on a.salary = c.sal
group by a.department_id

方法三：利用子查询，同样是表的一步步条件筛选，只不过是用在子查询这里，同样最后分组
select department_id,min(salary)
from employees
where department_id = (select department_id from employees d join
(select min(a.sal) as sal from employees b join
(select max(salary) as sal,department_id from employees group by department_id)a on a.sal = b.salary
)c
on d.salary = c.sal
)
group by department_id

个人推荐用方法二比较好，因为代码量相比较方法三少，更比方法一易懂；
方法一相对方法二难懂一些，主要在于rownum这个上面，方法二和方法三是相似的；
总之方法是多种多样的，就看你怎么用而已。