mysql对一个表的嵌套查询

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    department_id INT
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = employees.department_id
);

SELECT d.name AS department_name, (
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.id
) AS employee_count
FROM departments d;

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

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = employees.department_id
)
LIMIT 10 OFFSET 0;