備注:測試數(shù)據(jù)庫版本為MySQL 8.0

如需要scott用戶下建表及錄入數(shù)據(jù)語句怀愧，可參考:
scott建表及錄入數(shù)據(jù)sql腳本

一.需求

找到滿足這樣條件的員工: 即他的收入比緊隨其后聘用的員工要少渤涌。

二.解決方案

首先定義“未來”的意思瓮孙。必須給結(jié)果集排序，才能夠定義一個值在另一個值"后面".

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 窗口函數(shù)方法

用了分析函數(shù)淆储，這個就簡單多了

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)