当前位置：首页 > article >正文

【mysql】下一行减去上一行数据、自增序列场景应用

article 2024/9/24 13:17:00

背景

想获取if_yc为1连续账期数据
在这里插入图片描述

思路

获取所有if_yc为1的账期数据
下一行减去上一行账期，如果为1则为连续，不等于1就为断档
获取不等于1的最小账期，就是离当前账期最近连续账期

代码

以下为mysql语法：

select acct_month
from(
select (@m:=@m+1) rn,acct_month
from(
select '202311' acct_month,1 if_yc
union all 
select '202310',1
union all 
select '202309',1
union all 
select '202308',0
union all 
select '202307',1
union all 
select '202306',1) a,(select @m:=0) b
where if_yc = 1 
order by acct_month desc 
) m ,
(select min(t1.rn)+1 rn1-- ,t1.acct_month-t2.acct_month
from (
select (@i:=@i+1) rn,acct_month
from(
select '202311' acct_month,1 if_yc
union all 
select '202310',1
union all 
select '202309',1
union all 
select '202308',0
union all 
select '202307',1
union all 
select '202306',1) a,(select @i:=0) b
where if_yc = 1 
order by acct_month desc ) t1 ,
(
select (@j:=@j+1) rn,acct_month
from(
select '202311' acct_month,1 if_yc
union all 
select '202310',1
union all 
select '202309',1
union all 
select '202308',0
union all 
select '202307',1
union all 
select '202306',1) a,(select @j:=-1) b
where if_yc = 1 
order by acct_month desc 
) t2 where t1.rn = t2.rn and t1.acct_month-t2.acct_month <> 1 ) n 
where rn < n.rn1