# 法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