-- 思路:mysql8.0以下版本不开窗,用户,访问时间排序,利用自增Id,差序关联 -- 用户访问时间-下次访问时间,差值=1为连续访问,差值>1为跳空,差值>5为连续5天未登录 set @id_row:=0; set @id_next_row:=0; select * from (select t1.user_id,t1.next_login,t2.login_date, datediff(t1.next_login,t2.login_date) as diff, t1.id_next_row,t2.id_row from (select user_id, login_date as next_login, (@id_next_row:=@id_next_row+1) as id_next_row from user_login order by user_id,login_date) t1 left join (select user_id,login_date, (@id_row:=@id_row+1) as id_row from user_login order by user_id,login_date) t2 on t1.id_next_row = t2.id_row+1 and t1.user_id=t2.user_id ) t where diff >5
点赞 评论

相关推荐

不愿透露姓名的神秘牛友
07-25 15:14
点赞 评论 收藏
分享
牛客网
牛客网在线编程
牛客网题解
牛客企业服务