mysql - 如图所示，查出每个部门工资最高的前三位员工的信息

Question

如图所示，查出每个部门工资最高的前三位员工的信息

Answer 1

select e1.dept_id,d.name,e1.salary    
    from employee e1 
    left join dept d on e1.dept_id=d.id    
where      
   (  
    select count(1)     
    from employee e2     
    where e2.dept_id=e1.dept_id and e2.salary>=e1.salary  
   ) <=3 /*这里的数值表示你想取前几名*/

order by dept_id, sal desc;  

Answer 2

SELECT a.id FROM employee AS a
INNER JOIN dept as b ON a.dept_id=b.id
GROUP BY b.name ORDER BY SUM(a.salary)