orcl查询部门平均工资最高和最低的部门名称
时间: 2023-12-14 14:38:56 浏览: 39
可以使用以下SQL语句查询部门平均工资最高和最低的部门名称:
```
SELECT department_name, AVG(salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY department_name
HAVING AVG(salary) = (SELECT MAX(avg_salary) FROM (
SELECT AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
))
OR AVG(salary) = (SELECT MIN(avg_salary) FROM (
SELECT AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
));
```
其中,我们首先通过`JOIN`将`employees`表和`departments`表连接起来,以便获取每个部门的名称。然后,我们使用`GROUP BY`将结果按照部门名称进行分组,并计算每个部门的平均工资。接着,我们使用`HAVING`过滤出平均工资等于最高或最低值的部门。最后,我们使用子查询来获取全局最高和最低平均工资,并将它们与每个部门的平均工资进行比较。