Department Highest Salary

发表于   |   分类于 ， ，

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

描述

找出每个部门里 Salary 最大的人

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 方法一

select 
	Department,Name as Employee,tmp.Salary
from 
	Employee 
	left join 
		(
		select 
			max(Salary) as Salary,DepartmentId,Department.Name as Department 
		from 
			Employee,Department 
		where 
			Employee.DepartmentId = Department.Id group by departmentId
		) tmp 
	on 
		(Employee.Salary = tmp.Salary and Employee.DepartmentId = tmp.departmentId) 
where 
	Department is not null
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 方法二

select 
	Department.Name as Department, Employee.Name as Employee, Employee.Salary 
from 
	Employee, Department 
where 
	Employee.DepartmentId = Department.Id 
	and not exists 
	(
	select 1 from Employee e 
	where e.Salary > Employee.Salary and e.DepartmentId = Employee.DepartmentId
	)
1
2
3
4
5
6
7
8
9
10
-- 方法三

select 
	Department.Name as Department, Employee.Name as Employee, Employee.Salary 
from 
	Employee, Department 
where 
	Employee.DepartmentId = Department.Id 
	and (DepartmentId, Salary) in
	(select DepartmentId, max(Salary) from Employee group by DepartmentId)