leetcode-184-Department-Highest-Salary

描述


The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

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

The Department table holds all departments of the company.

1
2
3
4
5
6
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.

1
2
3
4
5
6
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+

分析


找出每个部门里工资最高的员工

解决方案(MySQL)


看到题目,我心想,不就是一道典型的作业题嘛,于是写了下面的 SQL 语句:

1
2
3
4
5
# Write your MySQL query statement below
SELECT
d.Name as Department, e.Name as Employee, MAX(Salary) as Salary
FROM Employee e, Department d WHERE e.DepartmentId=d.Id
GROUP BY e.DepartmentId

其实有一个方面我没有考虑到,每个部门里,工资最高的员工,有可能不止一个,所以有这样的错误信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Input: 

{"headers": {"Employee": ["Id"
"Name"
"Salary"
"DepartmentId"]
"Department": ["Id"
"Name"]}
"rows": {"Employee": [[1
"Joe"
60000
1]
[4
"Max"
60000
1]]
"Department": [[1
"IT"]]}}

Output:

{"headers": ["Department", "Employee", "Salary"], "values": [["IT", "Joe", 60000]]}

Expected:

{"headers": ["Department", "Employee", "Salary"], "values": [["IT", "Joe", 60000], ["IT", "Max", 60000]]}

接下来就明白应该怎么写了吧,最终 Accepted 的 SQL 语句:

1
2
3
4
5
6
7
8
# Write your MySQL query statement below
SELECT d.Name AS Department, e.Name AS Employee, e.Salary AS Salary
FROM Employee AS e, Department AS d
WHERE e.DepartmentId=d.Id AND e.Salary >= ALL(
SELECT tmp.Salary
FROM Employee AS tmp
WHERE tmp.DepartmentId=e.DepartmentId
)

SQL 还是比较好理解的,就不解释了。

题目来源