MySQL 查找同一组或分区中行的差

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

一.需求

返回每个员工的deptno、ename和sal以及与同一部门(即deptno值相同)的员工间的sal之差，
该差值在当前员工及同部门内紧随其后聘用的员工间计算而来。
对于每个部门中最新聘用的员工，这个差值为"N/A"。

二.解决方案

需要通过标量子查询查找到 下一个员工的工资，然后再来进行计算
MySQl 8.0开始引入了lag 和lead等窗口函数，可以更为简单的访问上一行和下一行

2.1 标量子查询方法

通过两层标量子查询，获取到下一个员工的工资，然后再进行计算

select deptno,ename,hiredate,sal,
       coalesce(cast(sal - next_sal as char(10)) ,'N/A') as diff
    from (
  select e.deptno,
         e.ename,
         e.hiredate,
         e.sal,
         (select min(sal) from emp d
            where d.deptno = e.deptno
              and d.hiredate = 
                   ( select min(hiredate) from emp d
                        where e.deptno = d.deptno
                          and d.hiredate > e.hiredate)) as next_sal
    from emp e
         ) x;

测试记录

mysql>
mysql> select deptno,ename,hiredate,sal,
    ->        coalesce(cast(sal - next_sal as char(10)) ,'N/A') as diff
    ->     from (
    ->   select e.deptno,
    ->          e.ename,
    ->          e.hiredate,
    ->          e.sal,
    ->          (select min(sal) from emp d
    ->             where d.deptno = e.deptno
    ->               and d.hiredate =
    ->                    ( select min(hiredate) from emp d
    ->                         where e.deptno = d.deptno
    ->                           and d.hiredate > e.hiredate)) as next_sal
    ->     from emp e
    ->          ) x;
+--------+--------+------------+---------+----------+
| deptno | ename  | hiredate   | sal     | diff     |
+--------+--------+------------+---------+----------+
|     20 | SMITH  | 1980-12-17 |  800.00 | -2175.00 |
|     30 | ALLEN  | 1981-02-20 | 1600.00 | 350.00   |
|     30 | WARD   | 1981-02-22 | 1250.00 | -1600.00 |
|     20 | JONES  | 1981-04-02 | 2975.00 | -25.00   |
|     30 | MARTIN | 1981-09-28 | 1250.00 | 300.00   |
|     30 | BLAKE  | 1981-05-01 | 2850.00 | 1350.00  |
|     10 | CLARK  | 1981-06-09 | 2450.00 | -2550.00 |
|     20 | SCOTT  | 1987-06-13 | 3000.00 | N/A      |
|     10 | KING   | 1981-11-17 | 5000.00 | 3700.00  |
|     30 | TURNER | 1981-09-08 | 1500.00 | 250.00   |
|     20 | ADAMS  | 1987-06-13 | 1100.00 | N/A      |
|     30 | JAMES  | 1981-12-03 |  950.00 | N/A      |
|     20 | FORD   | 1981-12-03 | 3000.00 | 1900.00  |
|     10 | MILLER | 1982-01-23 | 1300.00 | N/A      |
+--------+--------+------------+---------+----------+
14 rows in set (0.00 sec)

2.2 窗口函数

MySQL8.0之后有窗口函数，写起来就会方便很多

select deptno,ename,hiredate,sal,
       case when next_sal = 'N/A' then 'N/A' else cast(sal - next_sal as char(10)) end as diff
from 
(
SELECT a.empno,
       a.ename,
       a.deptno,
       a.hiredate,
       a.sal,
       lead(sal, 1, 'N/A') over w as 'next_sal'
  FROM emp a
  window w  as (PARTITION BY a.deptno ORDER BY hiredate ASC)
) x;

测试记录

mysql> select deptno,ename,hiredate,sal,
    ->        case when next_sal = 'N/A' then 'N/A' else cast(sal - next_sal as char(10)) end as diff
    -> from
    -> (
    -> SELECT a.empno,
    ->        a.ename,
    ->        a.deptno,
    ->        a.hiredate,
    ->        a.sal,
    ->        lead(sal, 1, 'N/A') over w as 'next_sal'
    ->   FROM emp a
    ->   window w  as (PARTITION BY a.deptno ORDER BY hiredate ASC)
    -> ) x;
+--------+--------+------------+---------+-------+
| deptno | ename  | hiredate   | sal     | diff  |
+--------+--------+------------+---------+-------+
|     10 | CLARK  | 1981-06-09 | 2450.00 | -2550 |
|     10 | KING   | 1981-11-17 | 5000.00 | 3700  |
|     10 | MILLER | 1982-01-23 | 1300.00 | N/A   |
|     20 | SMITH  | 1980-12-17 |  800.00 | -2175 |
|     20 | JONES  | 1981-04-02 | 2975.00 | -25   |
|     20 | FORD   | 1981-12-03 | 3000.00 | 0     |
|     20 | SCOTT  | 1987-06-13 | 3000.00 | 1900  |
|     20 | ADAMS  | 1987-06-13 | 1100.00 | N/A   |
|     30 | ALLEN  | 1981-02-20 | 1600.00 | 350   |
|     30 | WARD   | 1981-02-22 | 1250.00 | -1600 |
|     30 | BLAKE  | 1981-05-01 | 2850.00 | 1350  |
|     30 | TURNER | 1981-09-08 | 1500.00 | 250   |
|     30 | MARTIN | 1981-09-28 | 1250.00 | 300   |
|     30 | JAMES  | 1981-12-03 |  950.00 | N/A   |
+--------+--------+------------+---------+-------+
14 rows in set (0.00 sec)