[LeetCode] DepartmentHighestSalary, problem solving report

[LeetCode] DepartmentHighestSalary, problem solving report
Question

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

Ideas

When I see this question, I first consider database cascade. The specific ideas are as follows:

Find the highest salary for each department.

select e.DepartmentId, MAX(e.Salary) as Salary, d.Name as Department from Employee as e inner join Department as d on e.DepartmentId = d.Id group by e.DepartmentId;

After the statement is executed, the generated table structure is as follows:

DepartmentId	Salary	Department
1	9000	IT
2	8000	Sales

2. cascade the temporary table and the Employee table generated above to find the fields required by the question.

Select t. department as Department, e. name as Employee, t. salary as Salary from Employee as e inner join (Table generated by 1-sql) as t on e. salary = t. salary and e. required mentid = t. departmentId;

AC SQL

The final ac SQL statement is as follows:

select t.Department as Department, e.Name as Employee, t.Salary as Salary from Employee as e inner join (select e.DepartmentId, MAX(e.Salary) as Salary, d.Name as Department from Employee as e inner join Department as d on e.DepartmentId = d.Id group by e.DepartmentId) as t on e.Salary = t.Salary and e.DepartmentId = t.DepartmentId;