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