MYSQL implements the continuous sign-in function and dissigns from the beginning of a day (SQL statement), mysqlsql

MYSQL implements the continuous sign-in function and dissigns from the beginning of a day (SQL statement), mysqlsql

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' is 0, indicating sign-in data, 1 indicates sign-In date Dictionary data ') ENGINE = InnoDB default charset = utf8

2. Insert test data. The check-in time is from 5.21 to 6.5. You can write live data. However, if you want to write the stored procedure, I am lazy. The focus should be on the code for getting the check-in data, which is the third point, haha

Insert into 'testsign' ('userid', 'username', 'signtime', 'type') values ('20140901', 'dictionary ', '2017-05-21 00:00:00 ', '1'); insert into 'testsign' ('userid', 'username', 'signature', 'type ') values ('123', 'dictionary ', '2017-05-22 00:00:00', '1'); insert into 'testsign' ('userid', 'username ', 'signtime', 'type') values ('20170901', 'dictionary ', '2017-05-23 00:00:00', '1 '); insert into 'testsign' ('userid', 'username', 'signtime', 'type') values ('20140901', 'dictionary ', '2017-05-24 00:00:00 ', '1'); insert into 'testsign' ('userid', 'username', 'signature', 'type ') values ('123', 'dictionary ', '2017-05-25 00:00:00', '1'); insert into 'testsign' ('userid', 'username ', 'signtime', 'type') values ('20170901', 'dictionary ', '2017-05-26 00:00:00', '1 '); insert into 'testsign' ('userid', 'username', 'signtime', 'type') values ('20140901', 'dictionary ', '2017-05-27 00:00:00 ', '1'); insert into 'testsign' ('userid', 'username', 'signature', 'type ') values ('123', 'dictionary ', '2017-05-28 00:00:00', '1'); insert into 'testsign' ('userid', 'username ', 'signtime', 'type') values ('20170901', 'dictionary ', '2017-05-29 00:00:00', '1 '); insert into 'testsign' ('userid', 'username', 'signtime', 'type') values ('20140901', 'dictionary ', '2017-05-30 00:00:00 ', '1'); insert into 'testsign' ('userid', 'username', 'signature', 'type ') values ('123', 'dictionary ', '2017-05-31 00:00:00', '1'); insert into 'testsign' ('userid', 'username ', 'signattime', 'type') values ('20140901', 'dictionary ', '2017-06-01 00:00:00', '1 '); insert into 'testsign' ('userid', 'username', 'signtime', 'type') values ('20140901', 'dictionary ', '2017-06-02 00:00:00 ', '1'); insert into 'testsign' ('userid', 'username', 'signature', 'type ') values ('20170901', 'dictionary ', '2017-06-03 00:00:00', '1'); insert into 'testsign' ('userid', 'username ', 'signattime', 'type') values ('20140901', 'dictionary ', '2017-06-04 00:00:00', '1 '); insert into 'testsign' ('userid', 'username', 'signtime', 'type') values ('20140901', 'dictionary ', '2017-06-05 00:00:00 ', '1'); insert into 'testsign' ('userid', 'username', 'signature', 'type ') values ('123', 'wu Xiaoshuang sign-In data', '2017-05-21 00:00:00 ', '0'); insert into 'testsign' ('userid ', 'username', 'signature', 'type') values ('000000', 'wu Xiaoshuang sign-In data', '2017-05-22 00:00:00 ', '0 '); insert into 'testsign' ('userid', 'username', 'signtime', 'type') values ('123', 'wu Xiaoshuang sign-In data ', '2017-05-23 00:00:00 ', '0'); insert into 'testsign' ('userid', 'username', 'signtime', 'type ') values ('123', 'wu Xiaoshuang sign-In data', '2017-05-24 00:00:00 ', '0'); insert into 'testsign' ('userid ', 'username', 'signature', 'type') values ('000000', 'wu Xiaoshuang sign-In data', '2017-05-25 00:00:00 ', '0 '); insert into 'testsign' ('userid', 'username', 'signtime', 'type') values ('123', 'wu Xiaoshuang sign-In data ', '2017-05-26 00:00:00 ', '0 ');

3. query consecutive sign-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')<=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 

Undisconnected data

Delete data on the 23rd, start from the 24th, and sign for three consecutive days.

The above section describes how to implement the continuous sign-in function of MYSQL from the very beginning of one day. I hope it will be helpful to you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!