著作权
备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本
一.需求
返回一个结果集，它包含每个部门中所有员工的姓名、所在部门、工资、聘用日期以及部门中最新聘用员工的工资。
应返回下列结果集:
+--------+--------+---------+------------+------------+
| deptno | ename | sal | hiredate | latest_sal |
+--------+--------+---------+------------+------------+
| 10 | MILLER | 1300.00 | 1982-01-23 | 1300.00 |
| 10 | KING | 5000.00 | 1981-11-17 | 1300.00 |
| 10 | CLARK | 2450.00 | 1981-06-09 | 1300.00 |
| 20 | SCOTT | 3000.00 | 1987-06-13 | 3000.00 |
| 20 | ADAMS | 1100.00 | 1987-06-13 | 3000.00 |
| 20 | FORD | 3000.00 | 1981-12-03 | 3000.00 |
| 20 | JONES | 2975.00 | 1981-04-02 | 3000.00 |
| 20 | SMITH | 800.00 | 1980-12-17 | 3000.00 |
| 30 | JAMES | 950.00 | 1981-12-03 | 950.00 |
| 30 | MARTIN | 1250.00 | 1981-09-28 | 950.00 |
| 30 | TURNER | 1500.00 | 1981-09-08 | 950.00 |
| 30 | BLAKE | 2850.00 | 1981-05-01 | 950.00 |
| 30 | WARD | 1250.00 | 1981-02-22 | 950.00 |
| 30 | ALLEN | 1600.00 | 1981-02-20 | 950.00 |
+--------+--------+---------+------------+------------+
latest_sal 中的值是“骑士值”，这是由于查找他们的路径与国际象棋中骑士(马)的路径相似，求结果的方法跟骑士走到新位置的方式一样:跳到一行，然后转向跳到另一列。要找到LATEST_SAL的正确值，必须先定位每个DEPTNO中最新HIREDATE所在的行，然后选择该行的SAL列。
二.解决方案
select e.deptno,
e.ename,
e.sal,
e.hiredate,
( select max(d.sal)
from emp d
where d.deptno = e.deptno
and d.hiredate =
(select max(f.hiredate)
from emp f
where f.deptno = e.deptno)) as latest_sal
from emp e
order by 1, 4 desc;
测试记录:
mysql> select e.deptno,
-> e.ename,
-> e.sal,
-> e.hiredate,
-> ( select max(d.sal)
-> from emp d
-> where d.deptno = e.deptno
-> and d.hiredate =
-> (select max(f.hiredate)
-> from emp f
-> where f.deptno = e.deptno)) as latest_sal
-> from emp e
-> order by 1, 4 desc;
+--------+--------+---------+------------+------------+
| deptno | ename | sal | hiredate | latest_sal |
+--------+--------+---------+------------+------------+
| 10 | MILLER | 1300.00 | 1982-01-23 | 1300.00 |
| 10 | KING | 5000.00 | 1981-11-17 | 1300.00 |
| 10 | CLARK | 2450.00 | 1981-06-09 | 1300.00 |
| 20 | SCOTT | 3000.00 | 1987-06-13 | 3000.00 |
| 20 | ADAMS | 1100.00 | 1987-06-13 | 3000.00 |
| 20 | FORD | 3000.00 | 1981-12-03 | 3000.00 |
| 20 | JONES | 2975.00 | 1981-04-02 | 3000.00 |
| 20 | SMITH | 800.00 | 1980-12-17 | 3000.00 |
| 30 | JAMES | 950.00 | 1981-12-03 | 950.00 |
| 30 | MARTIN | 1250.00 | 1981-09-28 | 950.00 |
| 30 | TURNER | 1500.00 | 1981-09-08 | 950.00 |
| 30 | BLAKE | 2850.00 | 1981-05-01 | 950.00 |
| 30 | WARD | 1250.00 | 1981-02-22 | 950.00 |
| 30 | ALLEN | 1600.00 | 1981-02-20 | 950.00 |
+--------+--------+---------+------------+------------+
14 rows in set (0.00 sec)
技术文档
热门文章
155-2731-8020