SQL之一天一个小技巧：如何使用HQL进行行值轮转

目录

0 需求

1 分析

2 小结

---

0 需求

你想返回每个员工的姓名、工资，以及下一个比当前员工高和低的工资值。如果没有找到更高或更低的工资值，你希望结果集可以“折回”（第一个 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

1 分析

窗口函数 LAG OVER 和 LEAD OVER 将分别返回当前行的上一行和下一行记录。“上一行”或“下一行”取决于 OVER 子句里的 ORDER BY 部分。我们首先按照 SAL 排序数据集，并提取出了当前行的上一行和下一行。

select ename,sal,
       lead(sal,1)over(order by sal) forward,
       lag(sal,1)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 值应为 表中最小的 SAL；而对于最小的 SAL，REWIND 值应为最大的 SAL。没有指定分区（即 OVER 子句后面跟一对空括号）的窗口函数 MIN OVER 和 MAX OVER 将分别返回最大和最小的 SAL。结果集如下所示。 

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

注意这里面 "折回"，采用了NVL()函数【NUL值转换函数】，NVL(X,Y)函数可以将第一个位置处X字段中的NULL值转换为第二个位置Y处制定的值。

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

2 小结

本文分析了一种行值进行轮转的技巧，主要使用lag(),lead()函数及NVL函数进行解决。