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

select c.dept_no,
c.emp_no,
c.salary maxSalary
from
(select a.emp_no,
       a.dept_no,
       b.salary,
       rank() over(partition by a.dept_no order by b.salary desc) as salrank
from dept_emp a
right join salaries b
on a.emp_no = b.emp_no) c
where c.salrank = 1


# select c.dept_no,
# c.emp_no,
# d.Maxsalary
# from
#     (select a.emp_no,
#     dept_no,
#     salary
#     from dept_emp a
#     left join salaries b
#     on a.emp_no = b.emp_no) c

# right join

#     (select dept_no,
#     max(salary) maxSalary
#     from dept_emp a
#     left join salaries b
#     on a.emp_no = b.emp_no
#     group by dept_no) d

# on c.dept_no = d.dept_no and c.salary = d.maxSalary

窗口函数不能取名rank.. 找错找了好久