mysql5.7查询连续登录/连续签到天数

1.DDL

CREATE TABLE `user_sign_record` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
  `sign_date` date DEFAULT NULL COMMENT '签到日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin

2.DML

insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-25');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (3,'2023-03-19');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (3,'2023-03-27');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-17');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (3,'2023-03-15');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-26');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (2,'2023-03-16');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-23');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (3,'2023-03-10');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-21');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-10');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-16');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (2,'2023-03-21');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-20');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (2,'2023-03-15');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (2,'2023-03-13');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-28');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-27');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (2,'2023-03-17');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (1,'2023-03-19');
insert  into `user_sign_record`(`user_id`,`sign_date`) values (2,'2023-03-12');

3 查询

表基础数据:

3.1 查询用户与登陆日期去重

select user_id,sign_date from user_sign_record group by user_id,sign_date order by user_id asc,sign_date desc;

结果:

3.2 算出sign_date字段与参照日期差的值，按用sign_date字段排序

select @rowno:=@rowno+1 ords,user_id,sign_date, 
datediff('2023-03-28',t1.sign_date)+1 d_value from (
select user_id,sign_date from user_sign_record group by user_id,sign_date order by user_id asc,sign_date desc
) t1 join (SELECT @rowno:=0) t2；

结果:

tip:sign_date是随机插入的测试数据,d_value是sign_date与参照日期(示例使用的是:2023-03-28,可以使用CURDATE()函数为当前日期)差的值

3.3 如果用户连续签到/登陆，则“日期差的值”和“排序” 的值是相等的

3.3.1 筛查结果

3.3.2 筛查是否距基准日期连续签到n天

n=4时

n=5时是不满足记录的

欢迎讨论