极客教程SQL 查询未来的行，如果有员工的工资低于紧随其后入职的同事，那么你希望把这些人找出来。我们先看一下如下所示的结果集。
SQL 查询未来的行 问题描述
如果有员工的工资低于紧随其后入职的同事，那么你希望把这些人找出来。我们先看一下如下所示的结果集。
ENAME SAL HIREDATE
---------- ---------- ---------
SMITH 800 17-DEC-80
ALLEN 1600 20-FEB-81
WARD 1250 22-FEB-81
JONES 2975 02-APR-81
BLAKE 2850 01-MAY-81
CLARK 2450 09-JUN-81
URNER 1500 08-SEP-81
MARTIN 1250 28-SEP-81
KING 5000 17-NOV-81
JAMES 950 03-DEC-81
FORD 3000 03-DEC-81
MILLER 1300 23-JAN-82
SCOTT 3000 09-DEC-82
ADAMS 1100 12-JAN-83
SMITH、WARD、MARTIN、JAMES 以及 MILLER 的工资低于紧随其后入职的同事，因此，他们就是你要找的查询结果。
SQL 查询未来的行 解决方案
首先要确定“未来”的含义。我们必须对结果集排序以便能明确判断某一行记录含有的值是否“晚”于另一行。
DB2、MySQL、PostgreSQL 和 SQL Server
使用子查询为每一位员工计算出如下的值。
- 入职比他晚、且工资更高的员工当中最早入职的那个人的入职日期。
- 入职比他晚的员工当中最早入职的那个人的入职日期。
如果上述两个日期相等，那么这个人就是我们要找的。
1 select ename, sal, hiredate
2 from (
3 select a.ename, a.sal, a.hiredate,
4 (select min(hiredate) from emp b
5 where b.hiredate > a.hiredate
6 and b.sal > a.sal ) as next_sal_grtr,
7 (select min(hiredate) from emp b
8 where b.hiredate > a.hiredate) as next_hire
9 from emp a
10 ) x
11 where next_sal_grtr = next_hire
Oracle
可以使用窗口函数 LEAD OVER 访问下一个入职的员工的工资。剩下的就非常简单了，只需要检查一下该工资值是否更高即可。
1 select ename, sal, hiredate
2 from (
3 select ename, sal, hiredate,
4 lead(sal)over(order by hiredate) next_sal
5 from emp
6 )
7 where sal < next_sal
SQL 查询未来的行 扩展知识
DB2、MySQL、PostgreSQL 和 SQL Server
标量子查询为每一位员工返回紧随其后入职的第一个人的 HIREDATE，以及入职时间更晚且工资更高的第一个人的 HIREDATE。下面展示了未经过滤处理的数据。
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
ENAME SAL HIREDATE NEXT_SAL_GRTR NEXT_HIRE
------- ------ --------- ------------- ---------
SMITH 800 17-DEC-80 20-FEB-81 20-FEB-81
ALLEN 1600 20-FEB-81 02-APR-81 22-FEB-81
WARD 1250 22-FEB-81 02-APR-81 02-APR-81
JONES 2975 02-APR-81 17-NOV-81 01-MAY-81
MARTIN 1250 28-SEP-81 17-NOV-81 17-NOV-81
BLAKE 2850 01-MAY-81 17-NOV-81 09-JUN-81
CLARK 2450 09-JUN-81 17-NOV-81 08-SEP-81
SCOTT 3000 09-DEC-82 12-JAN-83
KING 5000 17-NOV-81 03-DEC-81
TURNER 1500 08-SEP-81 17-NOV-81 28-SEP-81
ADAMS 1100 12-JAN-83
JAMES 950 03-DEC-81 23-JAN-82 23-JAN-82
FORD 3000 03-DEC-81 23-JAN-82
MILLER 1300 23-JAN-82 09-DEC-82 09-DEC-82
对于当前员工而言，入职时间比他晚、且工资更高的的人不一定就是紧随其后入职的第一人。下一步（也是最后一步）只返回 NEXT_SAL_GRTR（入职时间晚于当前员工、且工资更高的员工当中最早入职的那个人的 HIREDATE）等于 NEXT_HIRE（入职比他晚的员工当中最早入职的那个人的 HIREDATE）的行。
Oracle
窗口函数 LEAD OVER 正好可以解决这一类问题。LEAD OVER 不仅使得代码更具可读性，同时也让解决方案变得更灵活，因为我们可以传递一个参数给 LEAD OVER，告诉它需要往前看未来多少行的数据（默认值是 1 行）。在排好序的数据集里如果含有重复数据，那么这种情况下能够往前看到多于 1 行的数据是很重要的。
下面的例子展示了使用 LEAD OVER 来看“下一个”入职的员工的工资是多么方便。
select ename, sal, hiredate,
lead(sal)over(order by hiredate) next_sal
from emp
ENAME SAL HIREDATE NEXT_SAL
------- ------ --------- ----------
SMITH 800 17-DEC-80 1600
ALLEN 1600 20-FEB-81 1250
WARD 1250 22-FEB-81 2975
JONES 2975 02-APR-81 2850
BLAKE 2850 01-MAY-81 2450
CLARK 2450 09-JUN-81 1500
TURNER 1500 08-SEP-81 1250
MARTIN 1250 28-SEP-81 5000
KING 5000 17-NOV-81 950
JAMES 950 03-DEC-81 3000
FORD 3000 03-DEC-81 1300
MILLER 1300 23-JAN-82 3000
SCOTT 3000 09-DEC-82 1100
ADAMS 1100 12-JAN-83
最后，筛选出 SAL 小于 NEXT_SAL 的行。因为 LEAD OVER 默认往前看 1 行，如果 EMP 表里有重复数据，尤其是当同一天入职的员工多于一个人的情况下，入职日期相同的员工之间也会做 SAL 比较。这可能偏离了我们的预期。如果我们想要把每一个员工的 SAL 和下一个同事的做比较，并且明确要求屏蔽掉在同一天入职的其他员工，那么就需要用到下面的替代方案。
select ename, sal, hiredate
from (
select ename, sal, hiredate,
lead(sal,cnt-rn+1)over(order by hiredate) next_sal
from (
select ename,sal,hiredate,
count(*)over(partition by hiredate) cnt,
row_number()over(partition by hiredate order by empno) rn
from emp
)
)
where sal < next_sal
上述做法的关键在于找出从当前行到它应该与之比较的行之间的距离。例如，如果有 5 个重复行，那么它的第一行就需要跳过 5 行数据才能找到正确的 LEAD OVER 行。对于具有重复 HIREDATE 的员工而言，那么 CNT 代表了他们的 HIREDATE 一共在多少行里出现过。RN 的值代表了 DEPTNO 等于 10 的每一个员工的序号。该序号的生成按照 HIREDATE 分区，因此只有那些含有重复 HIREDATE 的员工才可能有大于 1 的 RN 值。生成序号的时候先基于 EMPNO 做了排序（我们只是随便选了 EMPNO 做排序的基准）。现在我们已经知道了有多少个重复项，而且每个重复项都有一个序号，那么与下一个 HIREDATE 的距离就是重复项的总数减去当前的序号再加 1，即“CNT-RN+1”。