选择每个职业的所有高薪员工

Question

我有这张桌子。我想选择每个职业中具有最高工资的所有员工。

​

​

我试了所有的方法，但似乎都不起作用。以下查询结果错误。

SELECT * FROM Employee WHERE EmployeeID IN (
SELECT EmployeeID FROM Employee  HAVING MAX(Salary) = Salary GROUP BY Profession)

Answer 1

您可以使用ROW_NUMBER窗口函数

SELECT *
FROM   (SELECT *,
               Row_number()OVER (PARTITION BY Profession ORDER BY Salary DESC) rn
        FROM   Employee) a
WHERE  rn = 1 

注意:当一个职业的最高工资中存在tie时，这将只返回一个，如果您需要tie记录，则使用DENSE_RANK

SELECT *
FROM   (SELECT *,
               Dense_rank()OVER (PARTITION BY Profession ORDER BY Salary DESC) rn
        FROM   Employee) a
WHERE  rn = 1 

如果您想修改子查询，那么(将返回tie记录)

SELECT *
FROM   Employee e1
WHERE  Salary = (SELECT Max(Salary)
                 FROM   Employee e2
                 WHERE  e1.Profession = e2.Profession) 

或者

SELECT *
FROM   Employee e1
WHERE  EXISTS (SELECT 1
               FROM   Employee e2
               WHERE  e1.Profession = e2.Profession
               HAVING Max(Salary) = e1.Salary) 

Answer 2

SELECT * 
FROM (
      SELECT * 
           , ROW_NUMBER() OVER (PARTITION BY Profession ORDER BY Salary DESC) rn 
      FROM Employee
      ) x
WHERE x.rn = 1

Answer 3

试试这个简单的查询吧！

SELECT 
       EmployeeID              ,
       EmployeeName            ,
       Profession              ,
       max(Salary)   AS 'Salary'
FROM 
       Employee
GROUP BY 
       Profession