mysql 的高级子查询

SELECT 
    d.department_name, 
    AVG(e.salary) AS average_salary
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id
GROUP BY 
    d.department_name
HAVING 
    AVG(e.salary) > (SELECT AVG(salary) FROM employees);

SELECT 
    d.department_name, 
    AVG(e.salary) AS average_salary
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id
GROUP BY 
    d.department_name
HAVING 
    AVG(e.salary) > (
        SELECT AVG(salary)
        FROM employees
        GROUP BY department_id
    );

SELECT 
    d.department_name, 
    AVG(e.salary) AS average_salary
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id
GROUP BY 
    d.department_name
HAVING 
    AVG(e.salary) > (
        SELECT AVG(salary)
        FROM employees
        WHERE department_id IN (
            SELECT department_id
            FROM employees
            GROUP BY department_id
            LIMIT 10 OFFSET 0
        )
    );