sqlserver LEAD和LAG函数(学习记录)

可用于sqlserver 2012以后的版本，主要是查询当前数据的前几条数据或后几条数据的值

例：

LAG函数 往上取，取前几条的数据

WITH T AS
(
SELECT 1 ID,10 NUM
UNION ALL
SELECT 1,20
UNION ALL
SELECT 1,30
UNION ALL
SELECT 2,40
UNION ALL
SELECT 2,50
UNION ALL
SELECT 2,60
)


SELECT 
ID,
NUM,
LAG(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs,--根据id分组num排序，取上一条的记录
LAG(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs,--根据id分组num排序，取上一条的记录
LAG(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs--根据id分组num排序，取上两条的记录，并且如果为空则默认为0
FROM T


--查询结果为
ID    NUM    OneArgs    TowArgs    ThressArgs
1     10      null        null          0
1     20      10          10            0
1     30      20          20            10
2     40      null        null          0
2     50      40          40            0
2     60      50          50            40

LEAD函数 往后取，取后几条的数据

WITH T AS
(
SELECT 1 ID,10 NUM
UNION ALL
SELECT 1,20
UNION ALL
SELECT 1,30
UNION ALL
SELECT 2,40
UNION ALL
SELECT 2,50
UNION ALL
SELECT 2,60
)



SELECT ID,NUM,
LEAD(NUM) OVER (PARTITION BY ID ORDER BY NUM) AS OneArgs,--根据id分组num排序，取下一条的记录
LEAD(NUM,1) OVER (PARTITION BY ID ORDER BY NUM) AS TowArgs,--根据id分组num排序，取下一条的记录
LEAD(NUM,2,0) OVER (PARTITION BY ID ORDER BY NUM) AS ThressArgs--根据id分组num排序，取下两条的记录，并且如果为空则显示为0
FROM T

------结果-------
ID    NUM    OneArgs    TowArgs    ThressArgs
1      10      20         20           30
1      20      30         30           0
1      30      null       null         0
2      40      50         50           60
2      50      60         60           0
2      60      null       null         0