mysql - 通过子查询找到第二高的薪水

Question

我有两个表 employees 和 departments。

Employees 表有姓名、薪水、department_id。部门表有department_id, department_name

我必须使用子查询显示第二高的员工(实际上是 employees 表中每个员工中唯一一个薪水第二高的员工)和他的 department_name

select 
    max(e.salary),
    d.department_name 
from oehr_employees e 
join oehr_departments d on(e.department_id = d.department_id) 
where e.salary not in(
    select max(salary) from oehr_employees
) 
group by department_name

试过做这个，但是只显示每个部门第二高的工资。不知道该怎么做:/

尝试寻找答案，但没有得到我想要的。

Answer 1

如果你想使用子查询来做，你可以像下面这样尝试获取薪水第二高的员工和部门的详细信息。

select e.name,e.salary,d.department_id,d.department_name
 from 
 oehr_employees e
 join oehr_departments d on e.department_id = d.department_id
 WHERE  e.salary IN (SELECT Max(salary) 
                  FROM   oehr_employees 
                  WHERE  salary NOT IN (SELECT Max(salary) 
                                        FROM   oehr_employees));