社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
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
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号开始算,连续签三天
4,完事了~~~~~
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!