系列文章目录
前言
前些天发现了一个巨牛的人工智能学习网站,通俗易懂,风趣幽默,忍不住分享一下给大家。点击跳转到网站,这篇文章男女通用,看懂了就去分享给你的码吧。
描述
有一个员工表dept_emp简况如下:
有一个薪水表salaries简况如下:
获取每个部门中当前员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号dept_no升序排列,以上例子输出如下:
第一步:每个部门最高的薪水
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;