可以通过lag()与lead() 函数来实现

用窗口函数实现。我举一个mysql例。

with cte1 as
(
select 1 as id, '张三'  as sname, '2022-01-01' as bdate,'2022-01-03' as edate 
union all
select 2 as id,'张三'  as sname, '2022-01-08' as bdate,'2022-01-10' as edate 
union all
select 3 as id,'张三'  as sname, '2022-01-17' as bdate,'2022-01-21' as edate 
union all
select 4 as id,'张三'  as sname, '2022-01-24' as bdate,'2022-01-25' as edate 
union all
select 5 as id,'张三'  as sname, '2022-01-27' as bdate,'2022-01-29' as edate 
union all
select 6 as id,'张三'  as sname, '2022-02-03' as bdate,'2022-02-10' as edate 
union all
select 7 as id, '张三'  as sname, '2022-02-15' as bdate,'2022-02-23' as edate 
)
select id,sname,bdate,edate, nextbdate,datediff(nextbdate,edate) as days from 
(
select *,last_value(bdate) over(order by id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)  as nextbdate
 from cte1
) as t1

复制

执行结果：

用lead函数也可以

with cte1 as
(
select 1 as id, '张三'  as sname, '2022-01-01' as bdate,'2022-01-03' as edate 
union all
select 2 as id,'张三'  as sname, '2022-01-08' as bdate,'2022-01-10' as edate 
union all
select 3 as id,'张三'  as sname, '2022-01-17' as bdate,'2022-01-21' as edate 
union all
select 4 as id,'张三'  as sname, '2022-01-24' as bdate,'2022-01-25' as edate 
union all
select 5 as id,'张三'  as sname, '2022-01-27' as bdate,'2022-01-29' as edate 
union all
select 6 as id,'张三'  as sname, '2022-02-03' as bdate,'2022-02-10' as edate 
union all
select 7 as id, '张三'  as sname, '2022-02-15' as bdate,'2022-02-23' as edate 
)
select id,sname,bdate,edate, datediff(nextbdate,edate) as days from 
(
select *,lead(bdate) over(order by id)  as nextbdate
 from cte1
) as t1

复制

结果和上面一样。

可以去掉序号1的再排序后插入临时表，再与原来表关联。