mysql 子查询如何运用

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

SELECT department_id
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);

SELECT *
FROM employees
WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees);

SELECT *
FROM (SELECT department_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department_id) AS dept_avg
WHERE avg_salary > 5000;

SELECT e.*
FROM employees e
JOIN (SELECT department_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department_id) AS dept_avg
ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;