自用笔记

第一步：每个部门最高的薪水

select 
  d.dept_no,max(s.salary) salary 
from 
  dept_emp d
join 
  salaries s
on 
  d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'
group by 
  d.dept_no;

得到表t1：
+---------+--------+
| dept_no | salary |
+---------+--------+
| d001 | 88958 |
+---------+--------+
1 row in set (0.01 sec)

第二步(将员工、部门、薪水整合在一张表内)：

select 
  d.emp_no,d.dept_no,s.salary 
from 
  dept_emp d
join 
  salaries s
on 
  d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01';

得到表2：
+--------+---------+--------+
| emp_no | dept_no | salary |
+--------+---------+--------+
| 10001 | d001 | 88958 |
| 10002 | d001 | 72527 |
+--------+---------+--------+
2 rows in set (0.00 sec)

第三步：(将t1和t2整合)

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;

得到最终结果：
+---------+--------+--------+
| dept_no | emp_no | salary |
+---------+--------+--------+
| d001 | 10001 | 88958 |
+---------+--------+--------+
1 row in set (0.01 sec)

全部代码

select 
  t1.dept_no,t2.emp_no,t1.salary 
from 
  (select 
    d.dept_no,max(s.salary) salary 
  from 
    dept_emp d
  join 
    salaries s
  on 
    d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01'
  group by 
    d.dept_no) t1
join 
  (select 
    d.emp_no,d.dept_no,s.salary 
  from 
    dept_emp d
  join 
    salaries s
  on 
    d.emp_no=s.emp_no and d.to_date='9999-01-01' and s.to_date='9999-01-01') t2
on 
  t1.dept_no=t2.dept_no and t1.salary=t2.salary
order by 
  t1.dept_no;