获取每个部门中当前员工薪水最高的相关信息
题目:
解题:
明确目的:
题目是要找到每个部门的最高工资的员工和其工资
方法1:
建立两个表:
外表作为主表
内表作为条件
外表 :将部门和员工 链接在一起查询内表
内表:查询部门中最大的工资的员工
SELECT d1.dept_no, d1.emp_no, s1.salary
FROM dept_emp as d1
INNER JOIN salaries as s1
ON d1.emp_no=s1.emp_no
AND d1.to_date='9999-01-01'
AND s1.to_date='9999-01-01'
WHERE s1.salary in
(
SELECT MAX(s2.salary)
FROM dept_emp as d2
INNER JOIN salaries as s2
ON d2.emp_no=s2.emp_no
AND d2.dept_no = d1.dept_no
)
ORDER BY d1.dept_no;
方法2:
构建两个表:
第一个表
选取每个部门的最高工资
selecy d.dept_no, max(s.salary) salary
from dept_emp d
join salaies s
on
d.emp_no = s.emp_no
group by
de.dept_no;
第二个表
将员工表,部门表合并:
select
d.emp_no, d.dept_no, s.salary
from
dept_emp d
join salaries s
on d.emp_no=s.emp_no;
将 创建的这两个表 取交集 可以得出:
每个部门的最高员工 工资
select
t1.dept_no, t2.emp_no, t1.salary
from
(表t1) t1
join
(表t2) t2
on
t1.dept_no = t2.dept_no and t1.salary = t2.salary
order by
t1.dept_no;
最后整合的代码:
select
t1.dept_no, t2.emp_no,t1.salary
from
(select d.dept_no, max(s.salary) salary
from dept_empd d
join salaries s
on d.emp_no = s.emp_no
group by d.dept_no) t1
join
(select
d.emp_no, d.dept_no, s.salary
from dept_empo d
join
salaries s
on d.emp_no = s.emp_no
) t2
on t1.dept_no = t2.dept_no
and t1.salary = t2.salary
order by
t1.dept_no;