MySQL 存取“未来”行

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

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

一.需求

找到满足这样条件的员工: 即他的收入比紧随其后聘用的员工要少。

二.解决方案

首先定义“未来”的意思。必须给结果集排序，才能够定义一个值在另一个值"后面".

2.1 子查询方法

第一个子查询 查询后面所有聘用日期且工资比这一行工资多的聘用日期(如果没找到，返回就是null)
第二个子查询 查询下一个聘用日期

如果第一个和第二个相等，就代表满足题目要求。
这个解题思路不错，与我最初的解题思路完全不一致。

select  ename, sal, hiredate
  from  (
select  a.ename, a.sal, a.hiredate,
        (select min(hiredate) from emp b
          where b.hiredate > a.hiredate
          and   b.sal > a.sal) as next_sal_grtr,
        (select min(hiredate) from emp b
          where b.hiredate > a.hiredate) as next_hire
  from emp a
       ) x
where next_sal_grtr = next_hire;

测试记录

mysql> select  ename, sal, hiredate
    ->   from  (
    -> select  a.ename, a.sal, a.hiredate,
    ->         (select min(hiredate) from emp b
    ->           where b.hiredate > a.hiredate
    ->           and   b.sal > a.sal) as next_sal_grtr,
    ->         (select min(hiredate) from emp b
    ->           where b.hiredate > a.hiredate) as next_hire
    ->   from emp a
    ->        ) x
    -> where next_sal_grtr = next_hire;
+--------+---------+------------+
| ename  | sal     | hiredate   |
+--------+---------+------------+
| SMITH  |  800.00 | 1980-12-17 |
| WARD   | 1250.00 | 1981-02-22 |
| MARTIN | 1250.00 | 1981-09-28 |
| JAMES  |  950.00 | 1981-12-03 |
| MILLER | 1300.00 | 1982-01-23 |
+--------+---------+------------+
5 rows in set (0.00 sec)

2.2 MySQL 8.0 窗口函数方法

用了分析函数，这个就简单多了

select  ename, sal, hiredate
  from  (
select  ename, sal, hiredate,
        lead(sal) over w as 'next_sal'
  from  emp
  window w as (order by hiredate) 
        ) x
where sal < next_sal;

测试记录

mysql> select  ename, sal, hiredate
    ->   from  (
    -> select  ename, sal, hiredate,
    ->         lead(sal) over w as 'next_sal'
    ->   from  emp
    ->   window w as (order by hiredate)
    ->         ) x
    -> where sal < next_sal;
+--------+---------+------------+
| ename  | sal     | hiredate   |
+--------+---------+------------+
| SMITH  |  800.00 | 1980-12-17 |
| WARD   | 1250.00 | 1981-02-22 |
| MARTIN | 1250.00 | 1981-09-28 |
| JAMES  |  950.00 | 1981-12-03 |
| MILLER | 1300.00 | 1982-01-23 |
+--------+---------+------------+
5 rows in set (0.00 sec)