问题:查询平均工资最低的部门信息
该问题中平均工资涉及employees表,部门信息涉及departments表,
可将该问题按步骤拆分:
①查询每个部门的平均工资
②查询平均工资最低的部门的平均工资
③根据平均工资最低的部门的平均工资去找平均工资最低的部门的部门编号
④根据平均工资最低的部门的部门编号去找部门信息
具体实现:
①查询每个部门的平均工资:
SELECT avg( salary ) ag
FROM departments d
JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id;
②查询平均工资最低的部门的平均工资:
SELECT min( ag )
FROM (
SELECT avg( salary ) ag
FROM departments d
JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id
) ag_dep; 这里必须取别名!
③根据平均工资最低的部门的平均工资去找平均工资最低的部门的部门编号:
SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg( salary ) = (
SELECT min( ag )
FROM (
SELECT avg( salary ) ag
FROM departments d
JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id
) ag_dep
);
④根据平均工资最低的部门的部门编号去找部门信息:
SELECT *
FROM departments
WHERE department_id =(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg( salary ) = (
SELECT min( ag )
FROM (
SELECT avg( salary ) ag
FROM departments d
JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id
) ag_dep
)
);