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

查询结果:

Sql server lead和lag

 lead/lag查询结果集分析:

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

看 nextName_IdAsc列的输出原理:

Sql server lead和lag

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

Sql server lead和lag

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

看 nextName_IdDesc列的输出原理:

Sql server lead和lag

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

,分配好的序号结果集如下:

Sql server lead和lag

 接着根据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降序分配序号,

分配好的序号如下

Sql server lead和lag

 返回结果集分析

Sql server lead和lag

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

同理看 preName_IdAsc列的输出原理:

Sql server lead和lag

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

分配好的序号如下(与nextName_IdAsc分配好的序号一样)

Sql server lead和lag

  接着根据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

Sql server lead和lag

上一篇:尚硅谷MongoDB基础教程(数据库精讲)


下一篇:python 读取 查询 更新 删除 sql2008 类及应用