力扣---高频SQL题---50---185.部门工资前三高的所有员工

一、题目

二、代码

# Write your MySQL query statement below
-- select Employee.id ,Employee.name , Employee.salary , Employee.departmentId ,Department.name as de_name  from Employee
-- join Department
-- where Employee.departmentId  = Department.id
-- order by departmentId ,salary 

select Department.name as Department , table2.user_name as Employee  ,table2.salary as Salary  from Department
join 
(
    select e1.salary as salary,e1.departmentId as id,e1.name  as user_name       
    from Employee as e1
    where 3>
    (
        select count(distinct salary) 
        from Employee as e2
        where e1.salary < e2.salary and e1.departmentId = e2.departmentId
    )
) as table2
on Department.id = table2.id

三、运行结果