Oracle 号段

1.用lead 和lag 可以获取相邻号段的值

SELECT rown,
       lag(ROWN) OVER(ORDER BY ROWN) PREVIOUS,
       LEAD(ROWN) OVER(ORDER BY ROWN) NEXT
  FROM (SELECT ROWNUM + 4 rown
          FROM DUAL
        CONNECT BY ROWNUM < 5);

返回结果如下

 

rown previous next

5                       6

6           5          7

7           6          8

8            7   

 

2.获取前n行的内容，其中2表示前2行，-1 表示无志时显示的默认值

SELECT rown,
       lag(ROWN,2,-1) OVER(ORDER BY ROWN) PREVIOUS,
       LEAD(ROWN ,2,-1) OVER(ORDER BY ROWN) NEXT
  FROM (SELECT ROWNUM + 4 rown
          FROM DUAL
        CONNECT BY ROWNUM < 5);

 

返回结果如下

rown previous next

5           -1            7

6           -1         8

7           5          -1

8            6          -1