欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MYSQL实现连续签到功能断签一天从头开始(sql语句)

程序员文章站 2022-03-07 12:30:00
1,创建测试表 create table `testsign` ( `userid` int(5) default null, `username`...

1,创建测试表

create table `testsign` ( 
 `userid` int(5) default null, 
 `username` varchar(20) default null, 
 `signtime` timestamp not null default current_timestamp on update current_timestamp, 
 `type` int(1) default '0' comment '为0表示签到数据,1表示签到日期字典数据' 
) engine=innodb default charset=utf8 

2,插入测试数据,签到时间为5.21号到6.5号,可以写活,但是要写存储过程,我比较懒,重点应该是取签到数据的代码,就是第三点,呵呵

insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-21 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-22 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-23 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-24 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-25 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-26 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-27 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-28 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-29 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-30 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-05-31 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-06-01 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-06-02 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-06-03 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-06-04 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('72164','字典','2017-06-05 00:00:00','1'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-21 00:00:00','0'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-22 00:00:00','0'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-23 00:00:00','0'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-24 00:00:00','0'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-25 00:00:00','0'); 
insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-26 00:00:00','0'); 

3,查询连续签到数据

select * from testsign where type=0 and 
 date_format(signtime,'%y%m%d')>( 
 select ifnull(max(date_format(signtime,'%y%m%d')),"20170520") from testsign where type=1 
 and date_format(signtime,'%y%m%d')<=date_add(now(), interval -1 day) 
 and date_format(signtime,'%y%m%d') not in ( 
    select date_format(signtime,'%y%m%d') from testsign where type=0 and userid=800675 
    ) 
 ) 
 and date_format(signtime,'%y%m%d')<='20170605' 
 and userid=800675 

未断数据

MYSQL实现连续签到功能断签一天从头开始(sql语句)

删掉23号数据,从24号开始算,连续签三天

MYSQL实现连续签到功能断签一天从头开始(sql语句)

以上所述是小编给大家介绍的mysql实现连续签到功能断签一天从头开始,希望对大家有所帮助