mysql 实现查询用户连续登录的最大天数

一、创建测试表

create table tmp_bzs_0526(
id VARCHAR(100) comment 'id',
pday VARCHAR(100) comment 'pday'
);

select * from tmp_bzs_0526;

二、插入测试数据

insert into tmp_bzs_0526(id,pday) values ('A','20200501');
insert into tmp_bzs_0526(id,pday) values ('A','20200502');
insert into tmp_bzs_0526(id,pday) values ('A','20200503');
insert into tmp_bzs_0526(id,pday) values ('A','20200504');
insert into tmp_bzs_0526(id,pday) values ('A','20200506');
insert into tmp_bzs_0526(id,pday) values ('A','20200507');
insert into tmp_bzs_0526(id,pday) values ('A','20200508');
insert into tmp_bzs_0526(id,pday) values ('A','20200511');
insert into tmp_bzs_0526(id,pday) values ('A','20200512');
insert into tmp_bzs_0526(id,pday) values ('B','20200429');
insert into tmp_bzs_0526(id,pday) values ('B','20200430');
insert into tmp_bzs_0526(id,pday) values ('B','20200501');
insert into tmp_bzs_0526(id,pday) values ('B','20200504');
insert into tmp_bzs_0526(id,pday) values ('B','20200506');
insert into tmp_bzs_0526(id,pday) values ('B','20201231');
insert into tmp_bzs_0526(id,pday) values ('B','20210101');

为保证测试数据全面，插入两条跨年的日期数据。

三、参考链接 MySQL中row_number的实现
-- 增加行号
SELECT
    (@row_number:=@row_number + 1) AS rn, s.id, s.pday
FROM
    tmp_bzs_0526 s,(select @row_number := 0) as t;

-- 分组编号
select max(num),id from (
select
s.pday,@c_day,
    @row_number:=case
        when DATEDIFF(s.pday,@c_day) = 1 then @row_number + 1 
        else 1
    end    as num,
        @c_day := s.pday as c_day,
    s.id
from
    tmp_bzs_0526 s,(select @row_number :=0, @c_day := '') as t
        order by s.id,s.pday) t1
        group by t1.id;

最终结果：