题解 | #获取每个部门中当前员工薪水最高的相关信息#

#先把两个表中有用的信息连接在一起
#select a.emp_no,a.dept_no,b.salary from dept_emp a join salaries b on a.emp_no=b.emp_no;
#然后用窗口函数进行排序
#select c.emp_no,c.dept_no,c.salary,row_number() over(partition by c.dept_no order by c.salary desc)
#r from(select a.emp_no,a.dept_no,b.salary from dept_emp a join salaries b on a.emp_no=b.emp_no)c;



select e.dept_no,e.emp_no,e.salary MaxSalary from
(select c.emp_no,c.dept_no,c.salary,row_number() over(partition by c.dept_no order by c.salary desc)
as r from(select a.emp_no,a.dept_no,b.salary from dept_emp a join salaries b on a.emp_no=b.emp_no)c)e
where e.r=1;
#小而美公司##字节跳动数据分析师面经##涂鸦智能实习#