sql hard

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

    1. select d.name Department, e.name Employee, e.Salary
    2. from employee e left join department d on e.departmentid = d.id
    3. where e.id in 
    4. (
    5.     select e1.id from employee e1 left join employee e2
    6.     on e1.departmentid = e2.departmentid and e1.salary < e2.salary
    7.     group by e1.id
    8.     having count(distinct e2.salary) <= 2
    9. )
    10. and e.departmentid in (select id from department)
    11. order by d.id asc, e.salary desc;

    笔记

    能用基本语法就用基本语法, 能不用函数就不用函数;

    这是一个 分组取前几名 的问题, 可以先 group by 然后用 having count() , 在这里, 先用了一个子查询, 查询出比自己工资高的最多只有 2 个的员工的 id, 那么, 这些个 id 就是各个部门中的薪资前三名.