這篇文章主要介紹了mysql實現(xiàn)連續(xù)簽到功能斷簽一天從頭開始，非常不錯，具有參考借鑒價值,需要的朋友可以參考下
1，創(chuàng)建測試表
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表示簽到數(shù)據(jù)，1表示簽到日期字典數(shù)據(jù)'? )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8
2，插入測試數(shù)據(jù)，簽到時間為5.21號到6.5號，可以寫活，但是要寫mysql，我比較懶，重點應(yīng)該是取簽到數(shù)據(jù)的代碼，就是第三點，呵呵
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','吳小雙簽到數(shù)據(jù)','2017-05-21?00:00:00','0');? insert?into?`testsign`?(`userid`,?`username`,?`signtime`,?`type`)?values('800675','吳小雙簽到數(shù)據(jù)','2017-05-22?00:00:00','0');? insert?into?`testsign`?(`userid`,?`username`,?`signtime`,?`type`)?values('800675','吳小雙簽到數(shù)據(jù)','2017-05-23?00:00:00','0');? insert?into?`testsign`?(`userid`,?`username`,?`signtime`,?`type`)?values('800675','吳小雙簽到數(shù)據(jù)','2017-05-24?00:00:00','0');? insert?into?`testsign`?(`userid`,?`username`,?`signtime`,?`type`)?values('800675','吳小雙簽到數(shù)據(jù)','2017-05-25?00:00:00','0');? insert?into?`testsign`?(`userid`,?`username`,?`signtime`,?`type`)?values('800675','吳小雙簽到數(shù)據(jù)','2017-05-26?00:00:00','0');
3，mysql連續(xù)簽到數(shù)據(jù)
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>未斷數(shù)據(jù)</p><p style="text-align: center"><img alt="" src="https://img.php.cn/upload/article/000/000/194/508ad52af82822a6d8bbb30e7b894448-0.png"></p><p>刪掉23號數(shù)據(jù)，從24號開始算，連續(xù)簽三天<br></p><p style="max-width:90%"><img alt="" src="https://img.php.cn/upload/article/000/000/194/508ad52af82822a6d8bbb30e7b894448-1.png"></p>
? 版權(quán)聲明
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載。
THE END
