SQLserver LEAD和LAG函数
2020-11-11 本文已影响0人
michaelxwang
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Id] varchar(10),[Value] int )
Insert #T
select 'A',1 union all
select 'B',2 union all
select 'C',3 union all
select 'D',4 union all
select 'E',5
Go
--测试数据结束
SELECT *,
LEAD(Value, 1, 666) OVER (ORDER BY Value) AS LEADVALUE, --提前1行,默认值666
LAG(Value, 2, 888) OVER (ORDER BY Value) AS LAGVALUE --滞后2行,默认值888
FROM #T;
SELECT *,
LEAD(Value, 1) OVER (ORDER BY Value) AS LEADVALUE, --提前1行,默认值666
LAG(Value, 2) OVER (ORDER BY Value) AS LAGVALUE --滞后2行,默认值888
FROM #T;