极客教程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