获得各部门员工的最高2份工资

I have a simple employee table where i need to find difference between max of two salary in each department with maximum rank. Table columns i have: dept,sal,rank

我有一个简单的员工表，我需要在每个部门的最高职级中找出两个薪水的最大值之间的差异。我有:dept,sal,rank

Sample data could be

样本数据可以

Dept        Sal     Rank
Finance     10000   1
Finance     20000   2
Finance     11000   1
Finance     15000   3
Finance     45000   4
Finance     42000   4
Operations  17000   1
Operations  12000   1
Operations  15000   2
Operations  19000   2

Desired output is

期望的输出

Dept        Diff    Rank
Finance     3000    4
Operations  4000    2

I have managed to fetch top 2 record in a very very inefficient way. I am using mysql server.

我已经设法以一种非常低效的方式获得了前两项记录。我正在使用mysql服务器。

Here is the query

这是查询

SELECT *
FROM   emps s
WHERE 
        (
            SELECT  COUNT(*) 
            FROM    emps  f
            WHERE f.dept = s.dept AND 
                  f.rank >= s.rank
        ) <= 2

I need further help to get the output.

我需要进一步的帮助才能得到输出。

1 个解决方案

#1

You can give the following query a try:

您可以尝试以下查询:

SELECT Dept, MAX(Rank) AS Rank,
       SUM(CASE 
              WHEN rnk = 1 THEN Sal 
              WHEN rnk = 2 THEN -Sal 
              ELSE 0
           END) AS diff
FROM (
   SELECT @rnk := IF(@dept = Dept, @rnk + 1,
                     IF(@dept := Dept, 1, 1)) AS rnk,
          Dept, Sal, Rank                  
   FROM emp
   CROSS JOIN (SELECT @rnk := 0, @dept = '') AS vars
   ORDER BY Dept, Rank DESC, Sal DESC) AS t
GROUP BY Dept

The query uses variables to assign a rank number to each record depending on Rank and Sal values. The outer query consumes the variable values and performs conditional aggregation to calculate the difference between the first and the second ranking records.

查询使用变量来根据级别和Sal值为每个记录分配一个等级编号。外部查询使用变量值并执行条件聚合来计算第一个和第二个排名记录之间的差异。

Demo here

演示

#1