lag和lead函数

功能：将A列错位n行

语法：
LAG(字段名 [,下移行数[, default_value]]) OVER (
PARTITION BY 分区字段名,…
ORDER BY 排序字段名[ASC|DESC],…
)

LEAD(字段名 [,上移行数[, default_value]]) OVER (
PARTITION BY 分区字段名,…
ORDER BY 排序字段名[ASC|DESC],…
)

default_value：补充错位后得到的新列中的null，见下例中的”999“

with cte as (
    select 1 as 'A'
    union all
    select 2 as 'A'
    union all
    select 3 as 'A'
    union all
    select 4 as 'A'
    union all
    select 5 as 'A'
)
select A,
       lag(A, 1, 999) over (order by A)     AS lag1,-- 上移一行；补999
       lag(A, 2) over (order by A)          AS lag2,-- 上移两行；不补，则为null
       lead(A, 1, 999) over (order by A)    AS lead1,-- 下移一行；补999
       lead(A, 2, 999) over (order by A)    AS lead2, -- 下移两行；补999
       A + lag(A, 1, 999) over (order by A) AS 'A+lag1'
from cte;

查询结果