24 部门工资最高的员工

[astak16]

题目

找出每个部门工资最高的员工。

create table employee (
	id int,
	name varchar(255),
	salary int,
	departmentId int
);
insert into employee values
(1,'Joe', 70000, 1),
(2,'Jim', 90000, 1),
(3,'Henry', 80000, 2),
(4,'Sam', 60000, 2),
(5,'Max', 90000, 1);

create table department(id int, name varchar(255));
insert into department values(1, 'it'), (2, 'sales');

SQL：方法一

select department.name, employee.name, salary from employee
join department on employee.departmentId = department.id
where (departmentId, salary) in (
	select departmentId, max(salary) from employee group by departmentId
);

解析

两个字段也可以用 in 。

SQL：方法二

with temp as (
	select department.name as department, employee.name as employee, salary,
	dense_rank() over(partition by departmentId order by salary desc) as 排名
	from employee left join department on employee.departmentId = department.id
) select department, employee, salary from temp where 排名 = 1;

解析

• 使用 with 建立临
• 连接两表 department 和 employee 使用 dense_rank() 对 salary 进行排序。
  • partition by 的作用是分区