leetcode 185-JZTXT

部门工资前三高的所有员工

select d.name as Department, e.name as Employee, e.salary as Salary from Employee e left join Department d
on e.departmentId = d.Id

where 2 >= (
    select count(distinct e1.salary) from Employee e1
    where e.salary < e1.salary
    and e.departmentId = e1.departmentId
)

order by Department asc , Salary desc

select d.name as Department, e.name as Employee, e.salary as Salary from Employee e left join Department d
on e.departmentId = d.Id

where e.Id in
(
    select e1.Id
    from Employee as e1 left join Employee as e2
    on e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
    group by e1.Id
    having count(distinct e2.Salary) <= 2
)

order by Department asc , Salary desc

leetcode