[Leetcode] Department highest Salary The highest salary in the department

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

This problem gave us two tables, Employee table and Department table, let us find the highest salary in the Department of the person, in fact, the problem is second highest Salary and Combine two Tables the combination of the problem, We need to combine two tables and find the highest salary, so we first get two tables in the table and then mark the columns we need for the results table, and then we're looking for the highest salary, and we're using the MAX keyword, see the code below:

Solution One:

SELECT  as  as  from Employee E1 JOIN  on = WHERE inch (SELECTMAXfromWHERE= E2.) DepartmentID);

We can also use the Where to connect the two tables without using the JOIN keyword, and then find the highest salary method and the same as above:

Solution Two:

SELECT  as  as  from Employee E, Department D WHERE =  and = (SELECTMAXfromWHERE= d.id);

The following method does not use the MAX keyword, but instead uses the >= symbol to achieve the same effect as the Max keyword, see the code below:

Solution Three:

SELECT  as  as  from Employee E, Department D WHERE =  and >=  All (SELECTfromWHERE= d.id);

Similar topics:

Second Highest Salary

Combine Tables

Leetcode all in one topic summary (continuous update ...)

[Leetcode] Department highest Salary line maximum salary