题解 | #最长连续登录天数#

select cur_id user_id,format(max(cnt)+1,0) max_consec_days
from(
    select     
        if(@cur,@per:=@cur,@per:=null) per,
        @cur:=fdate cur,
        if(@cur_id,@per_id:=@cur_id,@per_id:=null) per_id,
        @cur_id:=user_id cur_id,
        if(datediff(@cur,@per)=1 and @per_id=@cur_id ,@cnt:=@cnt+1,@cnt:=0) cnt
    from( 
        select fdate,user_id
        from tb_dau
        where datediff(fdate,'2023-01-01')>=0 and datediff(fdate,'2023-01-01')<=30
        order by user_id asc,fdate asc
    )a,(select @cur:=null,@per:=null,@cnt:=0,@maxcnt:=0,@cur_id:=null,@per_id:=null)init
)b
group by user_id
order by user_id asc

不用窗口函数也能写