MySQL 轮换行值

客官°小女子只卖身不卖艺 2022-12-20 11:25 90阅读 0赞

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

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][scott_sql]

# 一.需求 #

返回每个员工的姓名和工资以及低于自己的最高工资和高于自己的最低工资。如果没有更高或最低的工资，则可能要求结果环绕(即第一个SAL显示最后一个SAL，反之亦然。)

要求输出如下:  
±-------±--------±--------±--------+  
| ename | sal | forward | rewind |  
±-------±--------±--------±--------+  
| SMITH | 800.00 | 950.00 | 5000.00 |  
| JAMES | 950.00 | 1100.00 | 800.00 |  
| ADAMS | 1100.00 | 1250.00 | 950.00 |  
| WARD | 1250.00 | 1300.00 | 1100.00 |  
| MARTIN | 1250.00 | 1300.00 | 1100.00 |  
| MILLER | 1300.00 | 1500.00 | 1250.00 |  
| TURNER | 1500.00 | 1600.00 | 1300.00 |  
| ALLEN | 1600.00 | 2450.00 | 1500.00 |  
| CLARK | 2450.00 | 2850.00 | 1600.00 |  
| BLAKE | 2850.00 | 2975.00 | 2450.00 |  
| JONES | 2975.00 | 3000.00 | 2850.00 |  
| SCOTT | 3000.00 | 5000.00 | 2975.00 |  
| FORD | 3000.00 | 5000.00 | 2975.00 |  
| KING | 5000.00 | 800.00 | 3000.00 |  
±-------±--------±--------±--------+

# 二.解决方案 #

## 2.1 子查询方法 ##

使用标量子查询，找到相对于每个工资的下一档工资和上一当工资:

select  e.ename, e.sal,
            coalesce(
               (select min(sal) from emp d where d.sal > e.sal),
               (select min(sal) from emp)
            ) as forward,
            coalesce(
               (select max(sal) from emp d where d.sal < e.sal),
               (select max(sal) from emp)
            ) as rewind
        from emp e
    order by 2;

测试记录

mysql> select  e.ename, e.sal,
        ->         coalesce(
        ->            (select min(sal) from emp d where d.sal > e.sal),
        ->            (select min(sal) from emp)
        ->         ) as forward,
        ->         coalesce(
        ->            (select max(sal) from emp d where d.sal < e.sal),
        ->            (select max(sal) from emp)
        ->         ) as rewind
        ->     from emp e
        -> order by 2;
    +--------+---------+---------+---------+
    | ename  | sal     | forward | rewind  |
    +--------+---------+---------+---------+
    | SMITH  |  800.00 |  950.00 | 5000.00 |
    | JAMES  |  950.00 | 1100.00 |  800.00 |
    | ADAMS  | 1100.00 | 1250.00 |  950.00 |
    | WARD   | 1250.00 | 1300.00 | 1100.00 |
    | MARTIN | 1250.00 | 1300.00 | 1100.00 |
    | MILLER | 1300.00 | 1500.00 | 1250.00 |
    | TURNER | 1500.00 | 1600.00 | 1300.00 |
    | ALLEN  | 1600.00 | 2450.00 | 1500.00 |
    | CLARK  | 2450.00 | 2850.00 | 1600.00 |
    | BLAKE  | 2850.00 | 2975.00 | 2450.00 |
    | JONES  | 2975.00 | 3000.00 | 2850.00 |
    | SCOTT  | 3000.00 | 5000.00 | 2975.00 |
    | FORD   | 3000.00 | 5000.00 | 2975.00 |
    | KING   | 5000.00 |  800.00 | 3000.00 |
    +--------+---------+---------+---------+
    14 rows in set (0.00 sec)

## 2.2 MySQL 8.0 窗口函数方法 ##

select ename ,sal ,
           coalesce(lead(sal) over w1,min(sal) over w2) forward,
           coalesce(lag(sal) over w1,max(sal) over w2) rewind
    from emp
    window w1 as (order by sal),
           w2 as ()
    ;

测试记录

mysql> select ename ,sal ,
        ->        coalesce(lead(sal) over w1,min(sal) over w2) forward,
        ->        coalesce(lag(sal) over w1,max(sal) over w2) rewind
        -> from emp
        -> window w1 as (order by sal),
        ->        w2 as ()
        -> ;
    +--------+---------+---------+---------+
    | ename  | sal     | forward | rewind  |
    +--------+---------+---------+---------+
    | SMITH  |  800.00 |  950.00 | 5000.00 |
    | JAMES  |  950.00 | 1100.00 |  800.00 |
    | ADAMS  | 1100.00 | 1250.00 |  950.00 |
    | WARD   | 1250.00 | 1250.00 | 1100.00 |
    | MARTIN | 1250.00 | 1300.00 | 1250.00 |
    | MILLER | 1300.00 | 1500.00 | 1250.00 |
    | TURNER | 1500.00 | 1600.00 | 1300.00 |
    | ALLEN  | 1600.00 | 2450.00 | 1500.00 |
    | CLARK  | 2450.00 | 2850.00 | 1600.00 |
    | BLAKE  | 2850.00 | 2975.00 | 2450.00 |
    | JONES  | 2975.00 | 3000.00 | 2850.00 |
    | SCOTT  | 3000.00 | 3000.00 | 2975.00 |
    | FORD   | 3000.00 | 5000.00 | 3000.00 |
    | KING   | 5000.00 |  800.00 | 3000.00 |
    +--------+---------+---------+---------+
    14 rows in set (0.00 sec)

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