LeetCode刷题笔记 - 184.Department Highest Salary

2018-10-30

184.Department Highest Salary

一、Description：

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

二、Solution:

解法一：

#Approach: Using JOIN and IN clause [Accepted]
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
	);

解法二：

select c.name as department,b.name as employee,a.salary 
from (select departmentid,max(salary) as salary 
        from employee
        group by departmentid) as a
inner join  employee as b on a.salary = b.salary
inner join department as c on b.departmentid = c.id；

解法三：

#Approach: Using JOIN and IN clause [Accepted]
SELECT
   t1.department AS Department,
   a.name AS Employee,
   t1.max_salary AS Salary
FROM employee a
JOIN (
       SELECT
          a.name AS Department,
          MAX(b.Salary) AS max_salary,
          b.departmentID AS DepartmentID
       FROM Department a
       JOIN Employee b
       ON a.Id = b.departmentid
       GROUP BY a.name
     ) t1
ON a.salary = t1.max_salary AND t1.DepartmentID = a.departmentID

解法四：

select d.name as 'Department',E.NAME AS 'Employee',e.salary
from department d,employee e
where d.id = e.departmentId
and e.salary = (select max(e1.salary) from employee e1 where e1.departmentId = d.id)

#？？？
Select d.Name as Department, e.Name as Employee, e.Salary
from Employee e, Department d
where e.DepartmentId = d.Id
and 0 >= (Select count(distinct(Salary)) from Employee
where Salary > e.Salary and
e.DepartmentId = DepartmentId)

三、总结知识点:

1、ALTER、UPDATE语法

之前的习题中已有employee表，只有Id、Name、Salary 这三列，没有DepartmentId，现在给原有的表里加一列进去，并赋值，语法如下：

alter table employee add DepartmentId int;
update employee set DepartmentId=1 where id=1 or id=4;
update employee set DepartmentId=2 where id=2;
update employee set DepartmentId=2 where id=3;
-- update employee set DepartmentId=1 where id=4;