题解 | #获取每个部门中当前员工薪水最高的相关信息#
获取每个部门中当前员工薪水最高的相关信息
http://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6
方法1
select eds.dept_no,eds.emp_no,ds.max_s
from
(#获取 部门、员工、薪资
select e.emp_no,e.dept_no,s.salary
from dept_emp e
inner join salaries s
on e.emp_no = s.emp_no
) as eds
,
(#获取 部门、薪资
select e2.dept_no,max(s2.salary) as max_s
from dept_emp e2
inner join salaries s2
on e2.emp_no = s2.emp_no
group by e2.dept_no
) as ds
where eds.salary = ds.max_s
and eds.dept_no = ds.dept_no
order by eds.dept_no;
方法2
select e.dept_no,e.emp_no,s.salary
from dept_emp e,salaries s,(
select e.dept_no,max(s.salary) max_salary
from dept_emp e,salaries s
where e.emp_no = s.emp_no
group by e.dept_no) as salary_tbl
where e.emp_no = s.emp_no
and e.dept_no = salary_tbl.dept_no
and s.salary = salary_tbl.max_salary
order by e.dept_no;
方法3：
where写法
select e.dept_no,e.emp_no,s.salary
from dept_emp e,salaries s
where e.emp_no = s.emp_no
and (e.dept_no,s.salary) in (
select e.dept_no,max(s.salary)
from dept_emp e,salaries s
where e.emp_no = s.emp_no
group by e.dept_no)
order by e.dept_no;
inner join写法
select e.dept_no,e.emp_no,s.salary
from dept_emp e
inner join salaries s
on e.emp_no = s.emp_no
and (e.dept_no,s.salary) in (
select e.dept_no,max(s.salary)
from dept_emp e
inner join salaries s
on e.emp_no = s.emp_no
group by e.dept_no
)
order by e.dept_no;
