mysql分组查询前几条

SELECT d.name AS department, e.name AS employee, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE (
    SELECT COUNT(*)
    FROM employees e2
    WHERE e2.department_id = d.id AND e2.salary >= e.salary
) <= 2
ORDER BY d.name, e.salary DESC;

WITH ranked_employees AS (
    SELECT d.name AS department, e.name AS employee, e.salary,
           DENSE_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rank
    FROM employees e
    JOIN departments d ON e.department_id = d.id
)
SELECT department, employee, salary
FROM ranked_employees
WHERE rank <= 2
ORDER BY department, salary DESC;