MYSQL如何實現(xiàn)連續(xù)簽到斷簽一天從頭開始的功能詳解(圖)

這篇文章主要介紹了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')&gt;(?  ?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)聲明
THE END
喜歡就支持一下吧
點贊15 分享