-- 思路：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