Sql server lead和lag

lead：取当前行所在位置后指定偏移量的行的值

如 lead(Name,1)取该行后第1行的Name值，lead(Name,10)取该行后第10行的Name值，如果没有返回null

 

lag：取当前行所在位置前指定偏移量的行的值

如 lag(Name,1)取该行前第1行的Name值，lag(Name,10)取该行前第10行的Name值，如果没有返回null

 

示例：

创建测试表

if object_id('tempdb..#TestTable','U') is not null
    drop table tempdb.#TestTable
go
create table #TestTable(Id int,Age int,Name nvarchar(20))

insert into #TestTable(Id,Age,Name)
values(1,10,'A1'),(2,10,'B1'),(3,10,'C1'),(4,41,'D1')

使用lead和lag查询如下：

select * from #TestTable
-- lead和lag函数示例
select Id
    ,LEAD(Name,1) over(order by Id) 'nextName_IdAsc'
    ,LEAD(Name,1) over(order by Id desc) 'nextName_IdDesc'
    ,LEAD(Name,1) over(partition by Age order by Id desc) 'nextName_PAge_IdDesc'
    ,LAG(Name,1) over(order by Id) 'preName_IdAsc'
    ,LAG(Name,1) over(order by Id desc) 'preName_IdDesc'
    ,LAG(Name,1) over(partition by Age order by Id desc) 'preName_PAge_IdDesc'
from #TestTable

查询结果：

 lead/lag查询结果集分析：

首先得知道，lead和lag是使用over处理后的结果集来取值的，over内部先根据partition分区（如果没有显示指定partition，则整个结果集为一个区），分好区后根据order by指定的排列顺序对分区完成的临时结果集进行排序，然后从1开始为排好序的每1行递增分配序号生成新的临时结果集B，lead（lag）就使用有序号的临时结果集B取后（前）几行的数据了

看 nextName_IdAsc列的输出原理：

取值逻辑为LEAD(Name,1) over(order by Id)，取值过程为先对表#TestTable分为1个区（因为没有显示指定partition by），然后根据id值(order by id)升序分配序号（序号从1开始递增），分配好的序号结果集B如下：

 接着根据Id定位初始位置所在的行，应用lead的逻辑即取下1行的Name值，即Id为3取下一行Name为D1，Id为2取到C1，Id为1取到B1，Id为4没有下一行返回null；

看 nextName_IdDesc列的输出原理：

nextName_IdDesc取值逻辑为LEAD(Name,1) over(order by Id desc)，先对表#TestTable分为1个区，然后根据Id值降序排序分配序号

，分配好的序号结果集如下：

 接着根据Id值定位初始位置所在行，应用lead的逻辑即取下1行的Name值，Id为3取下行为B1，id为2下行取A1，id为1下行取null，id为4下行取C1；

看 nextName_PAge_IdDesc列的输出原理：

nextName_PAge_IdDesc取值逻辑为LEAD(Name,1) over(partition by Age order by Id desc) ，先对表#TestTable根据Age分区，由表数据可知分为2个区（因为age只有10和41），然后在分好的每一个区内根据Id降序分配序号，

分配好的序号如下

 返回结果集分析

接着根据Id定位初始位置所在的行，应用lead的逻辑即取下1行的Name值，id为3取下行为B1，id为2取下行为A1，id为1取下行无返回null，id为4取下行返回null；

同理看 preName_IdAsc列的输出原理：

preName_IdAsc取值逻辑为LAG(Name,1) over(order by Id) ，先对表#TestTable分为1个区，然后根据Id值升序排序分配序号，分配好的序号结果集如下：

分配好的序号如下（与nextName_IdAsc分配好的序号一样）

  接着根据Id定位初始位置所在的行，应用lag的逻辑即取前1行的Name值，即Id为3取前一行Name为B1，Id为2前一行取到A1，Id为1无前一行返回null，Id为4取前一行返回C1；

 

同理，剩下的根据以上类推就能得到了

 

微软文档

https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver15