# 法1:窗口函数
# select dept_no,emp_no,salary maxSalary
# from (
#   select dept_no,de.emp_no,salary,rank()over(partition by dept_no order by salary desc) posn
#   from dept_emp de
#   join salaries s
#   on de.emp_no=s.emp_no
# ) t1
# where posn=1
# 法2:max+子查询--麻烦一些,但也是一种思路
with dept_maxS_table as (
  select dept_no,max(salary) maxSalary
  from dept_emp d 
  join salaries s
  on d.emp_no=s.emp_no
  group by dept_no 
)
select dept_no,d.emp_no,salary maxSalary
from dept_emp d
join salaries s
on d.emp_no=s.emp_no
where (dept_no,salary) in (
  select dept_no,maxSalary
  from dept_maxS_table
)
order by dept_no