[SQL]LeetCode184. 部门工资最高的员工 | Department Highest Salary

★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
➤微信公众号：山青咏芝（shanqingyongzhi）
➤博客园地址：山青咏芝（https://www.cnblogs.com/strengthen/）
➤GitHub地址：https://github.com/strengthen/LeetCode
➤原文地址：https://www.cnblogs.com/strengthen/p/10169993.html 
➤如果链接不是山青咏芝的博客园地址，则可能是爬取作者的文章。
➤原文已修改更新！强烈建议点击原文地址阅读！支持作者！支持原创！
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★

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

+----+-------+--------+--------------+
| 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.

+----+----------+
| 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.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

---

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department 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 在 Sales 部门有最高工资。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

---

236ms

 1 # Write your MySQL query statement below
 2 select 
 3 D.Name as Department,
 4 E.name as Employee,
 5 Salary
 6 from Employee E 
 7 join Department D 
 8 on E.DepartmentId=D.Id 
 9 where (E.DepartmentId,E.Salary) IN 
10 (select DepartmentId,max(Salary) 
11  from Employee 
12  group by DepartmentId)

---

239ms

1 select department.name as 'Department',employee.name as 'Employee',salary as 'Salary' from employee join department on employee.departmentid = department.id where (employee.departmentid,employee.salary) in (select departmentid, max(salary) from employee group by departmentid)

---

244ms

 1 # Write your MySQL query statement below
 2 
 3 SELECT
 4     Department.name AS 'Department',
 5     Employee.name AS 'Employee',
 6     Salary
 7 FROM
 8     Employee
 9         JOIN
10     Department ON Employee.DepartmentId = Department.Id
11 WHERE
12     (Employee.DepartmentId , Salary) IN
13     (   SELECT
14             DepartmentId, MAX(Salary)
15         FROM
16             Employee
17         GROUP BY DepartmentId
18     )
19 ;

---

252ms

 1 # Write your MySQL query statement below
 2 
 3 SELECT t2.name as Department, t1.Name as employee, t1.salary
 4 FROM employee t1
 5 JOIN department t2
 6 ON t1.departmentid = t2.id
 7 WHERE (t1.departmentid, t1.salary) in 
 8         (SELECT departmentid, MAX(salary)
 9         FROM employee
10         GROUP BY 1)
11 ORDER BY 1, 3 DESC

 

转载于:https://www.cnblogs.com/strengthen/p/10169993.html