[LeetCode] DepartmentHighestSalary, problem solving report

Source: Internet
Author: User

[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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.