[Leetcode] Department highest Salary--database knowledge (MySQL)

184. Department Highest Salary

The Employee table holds all employees. Every employee has an ID, a salary, and there are 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 has the highest salary in each of the departments. For the above tables, Max have the highest salary in the IT department and Henry have the highest salary in the Sales depart ment.

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

Solution One:

 select  d.name as  Department, E.name Span style= "COLOR: #0000ff" >as   Employee, e.salary  from   Employee E, Department D  where  e.departmentid =   d.id  and  (e.departmentid,e.salary) in   ( select  departmentid,max  (Salary) as  max  from  Employee group  by  DepartmentID) 

The problem-solving idea is to use the WHERE join clause to construct the filter condition, a new table Max is constructed in the clause sink, and the in operator is used to select DepartmentID and Salary,group by to group by DepartmentID to separate

The highest salary for each department.

Answer two:

SELECTD.name asDepartment, E.name asEmployee, E.salary fromEmployee E, (SELECTDepartmentID,Max(Salary) as Max  fromEmployeeGROUP  byDepartmentID) T, Department DWHEREE.departmentid=T.departmentid andE.salary=T.Max   andE.departmentid=D.id

And a slightly different, directly from the three tables selected, the newly constructed table is placed in the FROM clause inside, the others are directly replaced by aliases, with and join three conditions.

[Leetcode] Department highest Salary--database knowledge (MySQL)