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)
);

INSERT INTO departments (id, name) VALUES (1, 'HR'), (2, 'Engineering');

INSERT INTO employees (id, name, salary, department_id) VALUES
(1, 'Alice', 5000, 1),
(2, 'Bob', 6000, 1),
(3, 'Charlie', 7000, 2),
(4, 'David', 8000, 2);

SELECT d.name AS department_name, e.name AS employee_name, e.salary AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = e.department_id
);

SELECT d.name AS department_name, e.name AS employee_name, e.salary AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) em ON e.department_id = em.department_id AND e.salary = em.max_salary;

SELECT d.name AS department_name, e.name AS employee_name, e.salary AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = e.department_id
) LIMIT 1;