Mysql分组TopN

数据：

 需求：统计每个部门工资top2

SQL：

SELECT p1.name ,p1.salary ,p1.departmentId
FROM person p1
WHERE (
    SELECT count(p2.salary)
    FROM person p2
    WHERE p1.salary < p2.salary
      AND p1.departmentId = p2.departmentId
) < 2
ORDER BY p1.departmentId ,salary DESC;