在高薪部门工作的员工姓名

Question

我是SQL的新手，我正在尝试获得在该员工获得最高工资的部门工作的员工的姓名。

我成功地返回了工资最高的员工，但现在我想与他们的Dno和员工行的其余行执行联合，我该如何做呢？

SELECT DISTINCT Fname, Lname, salary
FROM employee 
WHERE Dno = (SELECT Dno FROM employee WHERE MAX(salary));

/*names of employees working in the department where the employee receives the high salary 

Answer 1

您已经接近了，但您应该检查WHERE子句中的薪水：

SELECT Fname, Lname, salary
FROM employee 
WHERE salary = (SELECT MAX(salary) FROM employee);

我们也可以在这里使用RANK或ROW_NUMBER，例如

WITH cte AS (
    SELECT Fname, Lname, salary, RANK() OVER (ORDER BY salary DESC) rnk
    FROM employee
)

SELECT Fname, Lname, salary
FROM cte
WHERE rnk = 1;

Answer 2

如果您想要一个与具有最高工资的部门处于同一部门的所有员工的列表，则不需要执行额外的联合，因为您只需在工资上使用ORDER BY，然后在WHERE子句中选择该最高工资级别的部门，如下所示：

SELECT  Fname,
        Lname,
        salary
FROM    employee
WHERE   Dno = (SELECT TOP 1 Dno FROM employee ORDER BY salary DESC)

Answer 3

您可以像这样嵌套select：

Select * from employee where deoptno in 
   ( Select deptno from empoloyee where salary = (Select max(salary) from employee))