一道面试题，关于oracle窗口函数lag、lead的应用

表A的数据如下：

ID                  NAME                                 COUNT
------------------- ------------------------------ -----------
1001                A                                       20
1002                B                                       10
1003                C                                       80
1004                D                                       50

 

写出一条SQL查询结果如下：

ID                  NAME                                 COUNT     DCOUNT     UCOUNT
------------------- ------------------------------ ----------- ---------- ----------
1002                B                                       10                      20
1001                A                                       20         10         50
1004                D                                       50         20         80
1003                C                                       80         50

 

实现SQL如下：

select A.ID,A.NAME,A.COUNT,LAG(A.COUNT,1) OVER(ORDER BY COUNT) DCOUNT,LEAD(A.COUNT,1) OVER(ORDER BY COUNT) UCOUNT FROM A;