SQL 查询未来的行|极客教程

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”。