MYSQL简单示例

昨天被问到一个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
;