184. Department Highest Salary
Table: Employee
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference columns) of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.
Table: Department
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table. It is guaranteed that department name is not NULL.
Each row of this table indicates the ID of a department and its name.
Write a solution to find employees who have the highest salary in each of the departments.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
Employee table:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.
Solution:
# Write your MySQL query statement below
# Department Employee
select
department.name as 'Department',
employee.name as 'Employee',
Salary
from Employee join Department ON Employee.DepartmentId = Department.Id
where (Employee.DepartmentId, Salary) in (
select DepartmentId, MAX(Salary) from Employee group by DepartmentId
# 最里面的子查询 先执行
# group by 按部分分组 max(Salary) 每组找最大的工资
)
;
where (Employee.DepartmentId, Salary) in ( 子查询结果 ): 如果某个员工的 (部门ID, 工资)
出现在刚才那张小表里，就选出来
select department.name as 'Department', employee.name as 'Employee', Salary: select 要显示的列 最终输出三列：
- 部门名
- 员工名
- 工资
GROUP BY 把多行变成一行
# Write your MySQL query statement below
select d.name as Department,
e.name as Employee,
e.salary as Salary
from Employee e
join Department d
on e.departmentId = d.id
join (
select departmentId, MAX(salary) as max_salary
from employee
group by departmentId
) t
on e.departmentId = t.departmentId
and e.salary = t.max_salary
Find the number of employees in each department.
# Write your MySQL query statement below select Department.name as Department, count(*) As Employee_count from Employee join Department on Employee.departmentId = Department.id Group By Department.name;output: