How does MYSQL realize the function of starting from scratch for one day of continuous check-in and break?
Editor to share with you how MYSQL to achieve continuous check-in break one day from the beginning of the function, I believe that most people do not know much, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!
1, create a test table
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 'indicates check-in data, 1 represents check-in date dictionary data') ENGINE=InnoDB DEFAULT CHARSET=utf8
2, insert test data, check-in time is 5.21 to 6.5, you can write live, but to write stored procedures, I am lazy, the focus should be on the check-in data code, that is, the third point, hehe
Insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164`signtime` dictionary', '2017-05-21 0000lname`, `signtime`, `type`) insert into `testsign` (`userid`, `username`, `signtime`, `type`) values (' 72164lane 'dictionary', '2017-05-22 00insert into 00lone'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164mm' Dictionary', '2017-05-002300insert into') Insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164`signtime` dictionary', '2017-05-24 0000lname`, `signtime`, `type`) insert into `testsign` (`userid`, `username`, `signtime`, `type`) values (' 72164lane 'dictionary', '2017-05-2500rig 00insert into'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164mm' dictionary', '2017-05-26 00lace 00lone') Insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164`signtime` dictionary', '2017-05-27 0000lname`, `signtime`, `type`) insert into `testsign` (`userid`, `username`, `signtime`, `type`) values (' 72164lane 'dictionary', '2017-05-2800insert into 00insert into'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164mm' Dictionary', '2017-05-2900mono') Insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164`signtime` dictionary', '2017-05-30 0000lname`, `signtime`, `type`); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values (' 72164lane 'dictionary', '2017-05-31 00rig 00insert into 00charge' 1'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164mm' dictionary', '2017-06-01 00insert into') Insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164`signtime` dictionary', '2017-06-02 00lname`, `signtime`, `type`); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values (' 72164lane 'dictionary,' 2017-06-03 00insert into 00insert into'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164mm' dictionary', '2017-06-04 00insert into') Insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('72164registered' Dictionary', '2017-06-05 00rig 00lb 00L'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('800675mm' Wu Xiaoshuang check-in data', '2017-05-2100JV 00JV 00JZ 0') Insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('800675 check-in data for Wu Xiaoshuang', '2017-05-22 0000RV 00R 00R 0'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values (' 800675mm'Wu Xiaoshuang check-in data', '2017-05-2300RV 00RV 00RO') Insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('800675 check-in data,' Wu Xiaoshuang check-in data', '2017-05-24 0000 rig 00lace 00lange 0000lname`, `signtime`, `type`) values (`userid`, `username`, `signtime`, `type`) values (' 800675Premier'Wu Xiaoshuang check-in data', '2017-05-2500RV 00Rd 00RO') Insert into `testsign` (`userid`, `username`, `signtime`, `type`) values ('800675 check-in data for Wu Xiaoshuang', '2017-05-26 00 0015 0')
3. Query continuous check-in data
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')
