本文主要介紹了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')<p>未斷數據</p><p style="text-align: center"><img alt="" src="https://img.php.cn/upload/article/000/054/025/66fcbfd56061542e397f1b3585768352-0.png"></p><p>刪掉23號數據，從24號開始算，連續簽三天<br></p><p style="max-width:90%"><img alt="" src="https://img.php.cn/upload/article/000/054/025/4fcaaa1da1e222451c7ccf2ea4766931-1.png"></p><p>大家學會了嗎？趕緊動手嘗試一下吧。<br></p><p>相關推薦：</p><p><a href="http://www.php.cn/mysql-tutorials-364332.html" target="_self">關于連續簽到的文章推薦8篇</a></p><p><a href="http://www.php.cn/mysql-tutorials-364332.html" target="_self">關于連續簽到的文章推薦8篇</a></p><p><a href="http://www.php.cn/mysql-tutorials-364332.html" target="_self">關于連續簽到的文章推薦8篇</a></p>
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
