mysql怎样查询平均工资最高 |mysql中数据库文件格式

SELECT department_name, AVG(salary) AS avg_salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id
GROUP BY departments.department_id
ORDER BY avg_salary DESC
LIMIT 1;

以上的查询语句通过JOIN将employees和departments表连接，根据department_id分组并求出平均工资avg_salary，并按avg_salary降序排列。最后使用LIMIT限制结果只返回一条，即查找平均工资最高的部门。

需要注意的是，以上的查询结果可能会出现多条平均工资相同的部门记录，如果需要查找全部平均工资最高的部门，需要将LIMIT改为使用子查询的方式。

SELECT department_name, avg_salary
FROM (
SELECT department_name, AVG(salary) AS avg_salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id
GROUP BY departments.department_id
) AS temp_table
WHERE avg_salary = (
SELECT MAX(avg_salary)
FROM (
SELECT AVG(salary) AS avg_salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id
GROUP BY departments.department_id
) AS temp_table
);

以上的查询语句使用了子查询的方式，先在一个临时表中求出全部部门的平均工资，再在外层查询中找出与最高平均工资相同的部门记录。