mysql 求各组中最大记录

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

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

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

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

SELECT d.department, COALESCE(MAX(e.salary), 0) AS max_salary
FROM departments d
LEFT JOIN employees e ON d.department = e.department
GROUP BY d.department;

-- 确保 department 和 position 列的数据类型正确
ALTER TABLE employees MODIFY COLUMN department VARCHAR(50);
ALTER TABLE employees MODIFY COLUMN position VARCHAR(50);