SQL12、获取每个部门当前薪水最高的员工的dept_no,emp_no,salary
获取每个部门中当前员工薪水最高的相关信息
http://www.nowcoder.com/questionTerminal/4a052e3e1df5435880d4353eb18a91c6
第一步：每个部门最高的薪水
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;
查看5道真题和解析
投递金山WPS等公司10个岗位 >