昨天被问到一个MYSQL题,怎可能难倒我,于是写了个和示例答案不一样的思路;
问题:有两张表,其中一张是员工工资表,另一张是部门表,求每个部门里工资最高的是谁,输出必须包含姓名、工资和部门三个字段;
测试数据代码
create table Employee (
Id string,
Name string,
Salary bigint,
DepartmentId bigint
);
insert into Employee
values
('1','Lisa',9000,1),
('2','Emily',8000,1),
('3','ELizabethI',9000,1),
('4','Angela',7000,2),
('5','Cindy',6000,2);
create table Department (
Id bigint,
Department string
);
insert into Department
values
(1,'IT'),
(2,'market');
解答代码 (自我感觉比标准代码简单多了)
select distinct
a.Name,a.Salary,b.Department
from(
select Name,Salary,DepartmentId,max(Salary) over(partition by DepartmentId) as max_salary from Employee
) a left join(
select Id,Department from Department
) b on a.DepartmentId=b.Id
where a.Salary=a.max_salary
;