mysql表子查询

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

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

SELECT *
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

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

SELECT e.*
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 *
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
    LIMIT 1
);