sql - 在列中查找值之前和之后的值

Question

我正在使用该lag()函数来查找先前在同一字段中出现的任何特定值的值。

是否有功能可以在字段值的下一个/之后找到一个？

示例数据：

id     desc
1       line1
1       line2
1       line 3
2       line 1
2       line 2
2       line 3

预期结果：

current   lg_desc      next
line 2    line1       line 3
line 2    line1       line 3

到目前为止我的查询是：

select  Desc as current,
              lag(Description)  
                over (partition by ID order by TimestampUTC) as lg_desc
        from  #temp1

标签： sqlsql-server

Answer 1

像这样使用引导功能

select  Desc as current,
              lag(Description)  
                over (partition by ID order by TimestampUTC) as lg_desc,
              lead(Description)  
                over (partition by ID order by TimestampUTC) as ld_desc
        from  #temp1