MySQL 找到骑士值

备注:测试数据库版本为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)