分析

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

开发工具与关键技术：Oracle sql*plus  PLSQL Developer

作者：朱海恩

撰写时间： 2019.03.16

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

我们一般在做题时，首先要对题目进行分析，知道有什么要查询的字段数据和有那些过滤要求，我们与下面的题目为列：

题目：查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email,

salary

（1）查询出 employee_id的员工的 last_name, department_id, email, salary

Select last_name, department_id, email, salary

From employees;

 

（2）查询各个部门中平均工资是多少，这是要对部门进行分组group  by

Select avg(salary)

From employees

Group by department_id;

 

（3）题目要求是部门中最高的平均工资，那么我们要对分组函数进行嵌套

Select max(avg(salary))

From employees

Group by department_id;

 

（4）接下来我们需要知道最高工资的部门id是多少，从而把（3）做子查询语句，因为子查询的查询字段是分组函数，所以左边也要用分组函数进行比较

Select department_id  from employees  group by department_id

Having avg(salary) = (

Select max(avg(salary))  from employees  group by department_id)

 

（5）查询出（4）对应的部门的 manager_id

Select manager_id  from departments

Where department_id = (

Select department_id  from employees  group by department_id

Having avg(salary) = (select max(avg(salary))  from employees

Group by department_id))

 

（6）最后，查询出 employee_id 为（5）查询的manager_id，如图1

         Select last_name, department_id, email, salary  FROM employees

         Where employee_id = (

         Select manager_id  from departments  where department_id = (

         Select department_id  from employees  group by department_id

Having avg(salary) = (select max(avg(salary))  from employees

         Group by department_id)));

图1

刚开始做的时候，想着manager_id和department_id在员工表都有，就直接把第（4）步省略掉，直接就manager_id，如图2

图2