每日Leetcode—SQL（5）

184. 部门工资最高的员工

Employee
Department
结果

方法一：

select d.Name Department, e.Name Employee, e.Salary
from Employee e,Department d
where e.Department=d.Id and e.Salary = 
(select max(Salary) from Employee where Department=d.Id)

方法二：

select d.Name Department, e.Name Employee, e.Salary
from Employee e join Department d on e.DepartmentId=d.Id
where e.DepartmentId=d.Id and (e.Salary, e.DepartmentId) 
in (select max(Salary),DepartmentId from Employee group by DepartmentId)

分析：该方法的执行时间比方法一有所提升

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

Employee
Department
结果

方法：

select d.Name Department, e1.Name Employee, e1.Salary
from Employee e1,Employee e2,Department d
where e1.DepartmentId=e2.DepartmentId and e1.Salary<=e2.Salary and e1.DepartmentId=d.Id 
group by e1.Name
having count(distinct e2.Salary) <= 3
order by d.Name,e1.Salary desc

分析：首先将表进行有条件内连，将比e2薪水少的e1行全部列出；进行group by，将e1中重复的数据去除；进行count聚合函数计算，distinct是确保前三高薪水的员工包含在最终的表内；最后进行排序。