leetcode---部门最高工资

nr7wwzry 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(348)

这个问题在这里已经有答案了：

sql仅选择列上具有最大值的行[重复]（27个答案）
两年前关门了。
我试图解决以下问题：employee表包含所有雇员。每个员工都有一个id，一个薪水，还有一个部门id列。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

department表包含公司的所有部门。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个sql查询来查找每个部门中工资最高的员工。在上表中，max在it部门的工资最高，henry在销售部门的工资最高。但是我想知道为什么下面的mysql查询不能得到正确的结果。

SELECT d.Name as Department, e.Name as Employee, e.Salary FROM Employee e
    left join Department d on e.DepartmentId=d.Id
    GROUP BY d.Name
    order by e.Salary desc limit 1

mysql

来源：https://stackoverflow.com/questions/51737026/leetcode-highest-salary-in-department

2条答案

按热度按时间

j2cgzkjk1#

试试这个：

SET @curRank = 0;  

SELECT * FROM (

SELECT  @curRank := @curRank + 1 AS rank, salary, e.id, e.name, DepartmentId
FROM dept d
 JOIN emp e ON e.DepartmentId = d.Id 
 ORDER BY salary DESC 

) AS tbl 

GROUP BY  departmentID

这会有用的。

赞(0）回复(0）举报 2021-06-23

fhity93d2#

此查询将执行您想要的操作：

SELECT d.Name AS Department, e.Name AS Employee, e.Salary
FROM Department d
JOIN Employee e
ON e.DepartmentID = d.Id AND 
   e.Salary = (SELECT MAX(Salary) 
               FROM Employee e2
               WHERE e2.DepartmentId = d.Id);

它将department表连接到该部门中工资最高的员工。
输出：

Department  Employee    Salary
Sales       Henry       80000
IT          Max         90000

sqlfiddle演示

赞(0）回复(0）举报 2021-06-23

我来回答

leetcode---部门最高工资

2条答案

相关问题

热门标签

最新问答