lead() over();lag() over()

最新推荐文章于 2024-06-27 20:34:47 发布

我只是个看客

最新推荐文章于 2024-06-27 20:34:47 发布

阅读量1k

点赞数

分类专栏：分析函数

本文链接：https://blog.csdn.net/wbaug20th/article/details/16498153

版权

分析函数专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SQL> select deptno,ename,sal,lead(sal) over (order by sal) next_val from emp;

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

SQL> select ename,hiredate,next_hd,next_hd - hiredate diff 
from (select deptno,ename,hiredate,lead(hiredate)over (order by hiredate) next_hd from emp);

ENAME      HIREDATE  NEXT_HD         DIFF
---------- --------- --------- ----------
SMITH      17-DEC-80 20-FEB-81         65
ALLEN      20-FEB-81 22-FEB-81          2
WARD       22-FEB-81 02-APR-81         39
JONES      02-APR-81 01-MAY-81         29
BLAKE      01-MAY-81 09-JUN-81         39
CLARK      09-JUN-81 08-SEP-81         91
TURNER     08-SEP-81 28-SEP-81         20
MARTIN     28-SEP-81 17-NOV-81         50
KING       17-NOV-81 03-DEC-81         16
JAMES      03-DEC-81 03-DEC-81          0
FORD       03-DEC-81 23-JAN-82         51
MILLER     23-JAN-82 19-APR-87       1912
SCOTT      19-APR-87 23-MAY-87         34
ADAMS      23-MAY-87

SQL> select ename,sal,lag(sal) over(order by sal) from emp;

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