利用mysql来实现的_利用MYSQL来实现连续签到功能 断签一天从头开始

【www.bbyears.com--mysql教程】

word-wrap: break-word; margin: 5px 0px; color: rgb(0, 0, 0); font-family: 'sans serif', tahoma, verdana, helvetica; font-size: 13.3333px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: 20px; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 1; word-spacing: 0px; -webkit-text-stroke-width: 0px;">1，创建测试表

CREATETABLE`testsign` ( 

 `userid`int(5)DEFAULTNULL, 

 `username`varchar(20)DEFAULTNULL, 

 `signtime`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP, 

 `type`int(1)DEFAULTƆ'COMMENT'为0表示签到数据，1表示签到日期字典数据' 

) ENGINE=InnoDBDEFAULTCHARSET=utf8

2，插入测试数据，签到时间为5.21号到6.5号，可以写活，但是要写存储过程，我比较懒，重点应该是取签到数据的代码，就是第三点，呵呵

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-21 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-22 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-23 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-24 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-25 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-26 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-27 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-28 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-29 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-30 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-05-31 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-06-01 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-06-02 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-06-03 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-06-04 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','字典',񟭑-06-05 00:00:00',Ƈ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','吴小双签到数据',񟭑-05-21 00:00:00',Ɔ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','吴小双签到数据',񟭑-05-22 00:00:00',Ɔ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','吴小双签到数据',񟭑-05-23 00:00:00',Ɔ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','吴小双签到数据',񟭑-05-24 00:00:00',Ɔ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','吴小双签到数据',񟭑-05-25 00:00:00',Ɔ'); 

insertinto`testsign` (`userid`, `username`, `signtime`, `type`)values(�','吴小双签到数据',񟭑-05-26 00:00:00',Ɔ');

3，查询连续签到数据

SELECT*FROMtestsignWHERETYPE=0AND 

 DATE_FORMAT(signtime,'%Y%m%d')>( 

 SELECTIFNULL(MAX(DATE_FORMAT(signtime,'%Y%m%d')),"20170520")FROMtestsignWHERETYPE=1 

 ANDDATE_FORMAT(signtime,'%Y%m%d')<=DATE_ADD(NOW(), INTERVAL -1DAY) 

 ANDDATE_FORMAT(signtime,'%Y%m%d')NOTIN( 

    SELECTDATE_FORMAT(signtime,'%Y%m%d')FROMtestsignWHERETYPE=0ANDuserid=800675 

    ) 

 ) 

 ANDDATE_FORMAT(signtime,'%Y%m%d')<=�' 

 ANDuserid=800675

未断数据

删掉23号数据，从24号开始算，连续签三天

以上所述是小编给大家介绍的MYSQL实现连续签到功能断签一天从头开始，希望对大家有所帮助。在此也非常感谢大家对本网站的支持！

本文来源：http://www.bbyears.com/shujuku/128680.html