为什么此选择不选择行？

Question

我试图选择'IT‘部门的最高收入者，但我一直没有选择行。薪资和名称在称为员工的表中，而department_name在一个名为部门的表中。有谁能解释一下为什么不选择行，我该怎么做？

SELECT first_name, last_name, salary, department_name
FROM employees
JOIN departments on departments.department_id = employees.department_id
WHERE salary = (SELECT max(salary) FROM employees WHERE department_name = 'IT'); 

Answer 1

为什么这个选择没有选择行？

查询失败，因为在department_name表中没有列employees。因此，您的子查询没有执行您预期的操作：

where salary = (SELECT max(salary) FROM employees WHERE department_name = 'IT'); 

如果要使用子查询执行此操作，则需要将其关联起来：

select e.first_name, e.last_name, e.salary, d.department_name
from employees e
inner join departments d on d.department_id = e.department_id
where 
    d.department_name = 'IT'
    and e.salary = (select max(e1.salary) 
                    from employees e1 
                    where e1.department_id = e.department_id); 

Answer 2

如果IT部门有一个以上的最高收入者，您可以使用dense_rank。

select   
    first_name, 
    last_name, 
    salary, 
    department_name
from
(
    select 
        first_name, 
        last_name, 
        salary, 
        department_name,
        dense_rank() over (partition by department_name order by salary desc) as rnk
    FROM employees e
    JOIN departments d
    on d.department_id = e.department_id
    where department_name = 'IT'
) val
where rnk = 1

Answer 3

使用row_number()：

SELECT t.*
FROM (SELECT emp.first_name, emp.last_name, dempt.salary, dempt.department_name,
             ROW_NUMBER() OVER(PARTITION BY dept.department_name ORDER BY dept.salary DESC) AS SEQ
      FROM employees emp JOIN 
           departments dept 
           ON dept.department_id = emp.department_id
      WHERE dempt.department_name = 'IT'
     ) t
WHERE seq = 1;

如果薪资有联系，那么您可以使用rank()代替。