MYSQL如何實現連續簽到斷簽一天從頭開始的功能

小編給大家分享一下MYSQL如何實現連續簽到斷簽一天從頭開始的功能，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

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如何實現連續簽到斷簽一天從頭開始的功能的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！