mysql子查询取最大值

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

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

SELECT department_id, salary AS max_salary
FROM employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

CREATE INDEX idx_department_salary ON employees(department_id, salary);

SELECT department_id, salary AS max_salary
FROM employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
)
ORDER BY department_id;