题解 | #获取每个部门中当前员工薪水最高的相关信息#
获取每个部门中当前员工薪水最高的相关信息
http://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6
-- 窗口函数方法
select
dept_no,
emp_no,
salary
from
(
select
a.emp_no,
b.dept_no,
salary,
max(salary) over (partition by b.dept_no) as maxsalary
from
salaries a
left join dept_emp b on a.emp_no = b.emp_no
) c
where
salary = maxsalary
-- 子查询方法
select
b.dept_no,
a.emp_no,
a.salary
from
salaries a
left join dept_emp b on a.emp_no = b.emp_no
where
(b.dept_no, a.salary) in (
select
b.dept_no,
max(a.salary) as maxsalary
from
salaries a
left join dept_emp b on a.emp_no = b.emp_no
group by
b.dept_no
)
order by dept_no
