mysql - 如何找到不同部门的第n个最高薪水？

Question

我正在尝试从列表中找出每个部门的第 n 大薪水。我能够使用聚合来完成基本的最小值和最大值，例如

Select DEPARTMENT, MAX(SALARY) FROM dept_salary
GROUP BY 1  

使用 MySQL 我能够根据个人而不是组合获得第 n 个薪水:

SELECT DISTINCT DEPARTMENT, SALARY FROM dept_salary
ORDER BY 2 DESC
LIMIT n,1

DEPARTMENT         SALARY
-------------------------
Customer Service   143937
Human Resources    141953
Customer Service   138637
Customer Service   137535
Customer Service   136548
Customer Service   135650
Marketing          135497
Customer Service   134893
Customer Service   133837
Customer Service   133569

如有任何关于如何让它工作的指示，我们将不胜感激。

Answer 1

对于一般的解决方案，变量是最简单的方法:

select ds.*
from (select ds.*,
             (@rn := if(@d = department, @rn + 1,
                        if(@d := department, 1, 1)
                       )
             ) as rn
      from (select distinct department, salary
            from dept_salary
           ) ds cross join
           (select @rn := 0, @d := '') params
      order by department, salary desc
     ) ds
where rn = 3;  -- for instance