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
未断数据
删掉23号数据,从24号开始算,连续签三天
以上所述是小编给大家介绍的mysql实现连续签到功能断签一天从头开始,希望对大家有所帮助
上一篇: MySQL时间盲注的五种延时方法实现
下一篇: Python如何配置环境变量详解