题解 | #获取每个部门中当前员工薪水最高的相关信息#
获取每个部门中当前员工薪水最高的相关信息
http://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6
解法一 纯 JOIN
select t.dept_no, j.emp_no, t.maxSalary from ( select d.dept_no as dept_no, max(s.salary) as maxSalary from dept_emp d join salaries s on s.emp_no = d.emp_no group by d.dept_no ) as t inner join ( select d.dept_no as dept_no, s.salary as salary, d.emp_no as emp_no from dept_emp d join salaries s on s.emp_no = d.emp_no ) as j on j.dept_no = t.dept_no and j.salary = t.maxSalary order by t.dept_no asc;
通过全部用例
运行时间 60ms
占用内存 6904KB
代码长而不难，比较容易理解。
第一个子查询先聚合，group by 聚合键 和 聚合函数 求出每个部门和它的最高薪水，问题在于没有emp_no，接下来我们补充这个字段。
第二个子查询 join 两张表，得到一张宽表，通过dept_no和salary定位到emp_no。
最后按照部门升序。
解法二
select t.dept_no, s.emp_no, t.maxSalary from ( select d.dept_no, max(s.salary) as maxSalary from dept_emp d,salaries s where d.emp_no = s.emp_no group by d.dept_no ) as t, salaries s, dept_emp d where t.maxSalary = s.salary and t.dept_no = d.dept_no and d.emp_no = s.emp_no order by t.dept_no asc
解法三 利用 MYSQL 8.0 之后的开窗函数
select dept_no, emp_no, maxSalary from ( select d.dept_no as dept_no,d.emp_no as emp_no,s.salary as salary, first_value(salary) over(partition by d.dept_no order by s.salary desc) as maxSalary from dept_emp d join salaries s on s.emp_no = d.emp_no ) as t where salary = maxSalary
通过全部用例
运行时间 52ms
占用内存 6776KB
运行时间 52ms
占用内存 6776KB
first_value(salary) over(partition by d.dept_no order by s.salary desc) as maxSalary
结果还不是我们想要的，每一行记录都会与聚合值拼接成一行，需要再次把 salary 等于最大薪资 maxSalary 的筛选出来
where salary = maxSalary
select dept_no, emp_no,salary as maxSalary from ( select d.dept_no as dept_no, d.emp_no as emp_no, s.salary as salary, rank() over(partition by d.dept_no order by s.salary desc) as ranking from dept_emp d join salaries s on s.emp_no = d.emp_no ) as t where t.ranking = 1;
