mysql 单表嵌套查询

SELECT department, MAX(salary) AS max_salary
FROM employees
GROUP BY department;

SELECT id, name, department, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = employees.department
);

-- 使用JOIN代替子查询
SELECT e1.id, e1.name, e1.department, e1.salary
FROM employees e1
JOIN (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) e2 ON e1.department = e2.department
WHERE e1.salary > e2.avg_salary;

-- 使用LIMIT限制结果集大小
SELECT id, name, department, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = employees.department
    LIMIT 1000
);