先补充两个内容 ALL和ANY的用法

ALL和ANY运算符

它将单个值于子查询返回的单列值进行比较，ALL运算
符必须以比较运算符开头，比如：“>=,>,<,=等等。”

any表示有任何一个满足就返回true，all表示全部都满足才返回true
all 父查询中的结果集大于子查询中每一个结果集中的值,则为真

我

查询平均工资高于公司平均工资的部门有哪些?

SELECT `department_id`
FROM `employees`
GROUP BY department_id
HAVING AVG(salary) > (
SELECT AVG(salary)
FROM employees
);

查询出公司中所有 manager 的详细信息.

SELECT *
FROM `employees`
WHERE employee_id = ANY(
SELECT DISTINCT manager_id
FROM employees
)

各个部门的最高工资中最低的那个部门的最低工资是多少

SELECT MIN(salary)
FROM `employees`
GROUP BY `department_id`
HAVING MAX(salary) = ANY(
SELECT MAX(salary)
FROM `employees`
GROUP BY `department_id`
);

查询平均工资最高的部门的 manager 的详细信息:

SELECT department_id
FROM`employees`
GROUP BY `department_id`
ORDER BY AVG(salary) DESC
LIMIT 1;


SELECT manager_id
FROM employees
WHERE department_id = (
SELECT department_id
FROM`employees`
GROUP BY `department_id`
ORDER BY AVG(salary) DESC
LIMIT 1
);


SELECT *
FROM `employees`
WHERE manager_id = (
SELECT manager_id
FROM `departments`
WHERE department_id = (
SELECT department_id
FROM`employees`
GROUP BY `department_id`
ORDER BY AVG(salary) DESC
LIMIT 1
)
);