是否根据重复的列值限制MySQL选择？

Question

考虑一下这个模式：

create table Employee(
    id integer, 
    name varchar(100), 
    position varchar(100), 
    id_department integer
);

create table Salary(
    id_employee integer, 
    year integer, 
    month integer, 
    salary float
);

create table Department(
    id integer, 
    name varchar(100)
);

我想收集那些根据每个部门的平均年薪排名在第三到第五位之间的员工，但我有点困惑。经过一番搜索，我找不到一个适合我的解决方案。

这是我目前所拥有的：

SELECT Employee.*, avg_salary
FROM Employee
INNER JOIN (
    SELECT id_employee, sum(salary) / count(distinct(year)) as avg_salary 
    FROM Salary 
    GROUP BY id_employee
)
AS T on Employee.id = id_employee 
ORDER BY Employee.id_department, avg_salary;

这会让我得到正确的员工排名，但是我如何才能只获得他们部门中排名3-5的员工呢？

我使用的是MySQL 5.x，但是升级到8.x不是问题。

Answer 1

在MySQL 8.0中，一种选择是使用排名函数；这需要将现有查询转换为子查询：

select *
from (
    select 
        e.*, 
        s.avg_salary, 
        row_number() over(partition by e.id_department order by s.avg_salary desc) rn
    from employee e
    inner join (
        select id_employee, sum(salary) / count(distinct(year)) as avg_salary 
        from salary 
        group by id_employee
    ) s on s.employee.id = e.id
) t
where rn between 3 and 5
order by id_department, avg_salary;