MySQL 找到骑士值

向右看齐 2022-12-21 00:42 90阅读 0赞

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][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)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb