SQL 行值轮转|极客教程

SQL 行值轮转，你想返回每个员工的姓名、工资，以及下一个最高和最低的工资值。如果没有找到更高或更低的工资值，你希望结果集可以“折回”（第一个 SAL 的前一行是最后一个 SAL；反之，最后一个 SAL 的下一行即是第一个 SAL）。

SQL 行值轮转问题描述

你想返回每个员工的姓名、工资，以及下一个最高和最低的工资值。如果没有找到更高或更低的工资值，你希望结果集可以“折回”（第一个 SAL 的前一行是最后一个 SAL；反之，最后一个 SAL 的下一行即是第一个 SAL）。你希望返回如下所示的结果集。

ENAME             SAL    FORWARD     REWIND
---------- ---------- ---------- ----------
SMITH             800        950       5000
JAMES             950       1100        800
ADAMS            1100       1250        950
WARD             1250       1250       1100
MARTIN           1250       1300       1250
MILLER           1300       1500       1250
TURNER           1500       1600       1300
ALLEN            1600       2450       1500
CLARK            2450       2850       1600
BLAKE            2850       2975       2450
JONES            2975       3000       2850
SCOTT            3000       3000       2975
FORD             3000       5000       3000
KING             5000        800       3000

SQL 行值轮转解决方案

对于 Oracle 用户而言，窗口函数 LEAD OVER 和 LAG OVER 使得本问题解决起来相对容易，而且代码可读性更好。对于其他数据库，可以使用标量子查询，不过 Tie 可能会带来问题。由于存在 Tie 的问题，对于不支持窗口函数的关系数据库管理系统，我们只能提供一个近似的解决方案。
DB2、SQL Server、MySQL 和 PostgreSQL
使用标量子查询为每一个工资值找到它的下一个和前一个的工资值。

 1  select e.ename, e.sal,
 2         coalesce(
 3           (select min(sal) from emp d where d.sal > e.sal),
 4           (select min(sal) from emp)
 5         ) as forward,
 6         coalesce(
 7           (select max(sal) from emp d where d.sal < e.sal),
 8           (select max(sal) from emp)
 9         ) as rewind
10    from emp e
11   order by 2

Oracle
使用窗口函数 LAG OVER 和 LEAD OVER 访问当前行的上一行和下一行记录。

1 select ename,sal,
2        nvl(lead(sal)over(order by sal),min(sal)over()) forward,
3        nvl(lag(sal)over(order by sal),max(sal)over()) rewind
4   from emp

SQL 行值轮转扩展知识

DB2、SQL Server、MySQL 和 PostgreSQL
标量子查询方案并没有真正解决本问题。它只是一个近似的方案，当两行记录包含相同的 SAL 时，该解决方案就会返回不正确的结果。不过，在没有窗口函数可用的情况下，它已经是最好的方案了。
Oracle
（默认情况下，除非有特别指定。）窗口函数 LAG OVER 和 LEAD OVER 将分别返回当前行的上一行和下一行记录。“上一行”或“下一行”取决于 OVER 子句里的 ORDER BY 部分。如果仔细阅读本解决方案的代码，我们会发现它首先按照 SAL 排序数据集，并提取出了当前行的上一行和下一行。

select ename,sal,
       lead(sal)over(order by sal) forward,
       lag(sal)over(order by sal) rewind
  from emp
　
ENAME             SAL    FORWARD     REWIND
---------- ---------- ---------- ----------
SMITH             800        950
JAMES             950       1100        800
ADAMS            1100       1250        950
WARD             1250       1250       1100
MARTIN           1250       1300       1250
MILLER           1300       1500       1250
TURNER           1500       1600       1300
ALLEN            1600       2450       1500
CLARK            2450       2850       1600
BLAKE            2850       2975       2450
JONES            2975       3000       2850
SCOTT            3000       3000       2975
FORD             3000       5000       3000
KING             5000                  3000

注意，员工 SMITH 的 REWIND 是 Null，而 KING 的 FORWARD 也是 Null；这是因为两个人的 SAL 分别是最低值和最高值。“问题”部分提到，FORWARD 或 REWIND 若出现 Null 值，则应该“折回”。这就意味着，对于最大的 SAL，FORWARD 值应为 EMP 表中最小的 SAL；而对于最小的 SAL，REWIND 值应为最大的 SAL。没有指定分区（即 OVER 子句后面跟一对空括号）的窗口函数 MIN OVER 和 MAX OVER 将分别返回最大和最小的 SAL。结果集如下所示。

select ename,sal,
       nvl(lead(sal)over(order by sal),min(sal)over()) forward,
       nvl(lag(sal)over(order by sal),max(sal)over()) rewind
  from emp
　
ENAME             SAL    FORWARD     REWIND
---------- ---------- ---------- ----------
SMITH             800        950       5000
JAMES             950       1100        800
ADAMS            1100       1250        950
WARD             1250       1250       1100
MARTIN           1250       1300       1250
MILLER           1300       1500       1250
TURNER           1500       1600       1300
ALLEN            1600       2450       1500
CLARK            2450       2850       1600
BLAKE            2850       2975       2450
JONES            2975       3000       2850
SCOTT            3000       3000       2975
FORD             3000       5000       3000
KING             5000       800        3000

LAG OVER 和 LEAD OVER 还有一个非常有用的功能，就是可以指定向前或者向后移动多少行。对于本例而言，我们只往前或往后移动了一行。如果你想往前移动 3 行，并且往后移动 5 行，做法非常简单。只需要指定移动值分别为 3 和 5 即可，如下所示。

select ename,sal,
       lead(sal,3)over(order by sal) forward,
       lag(sal,5)over(order by sal) rewind
  from emp
　
ENAME             SAL    FORWARD     REWIND
---------- ---------- ---------- ----------
SMITH             800       1250
JAMES             950       1250
ADAMS            1100       1300
WARD             1250       1500
MARTIN           1250       1600
MILLER           1300       2450        800
TURNER           1500       2850        950
ALLEN            1600       2975       1100
CLARK            2450       3000       1250
BLAKE            2850       3000       1250
JONES            2975       5000       1300
SCOTT            3000                  1500
FORD             3000                  1600
KING             5000                  2450