无码窝视频在线观看入口,亚洲成av人片在线观看www

主頁(yè) > 知識(shí)庫(kù) > MYSQL實(shí)現(xiàn)連續(xù)簽到功能斷簽一天從頭開始(sql語(yǔ)句)

MYSQL實(shí)現(xiàn)連續(xù)簽到功能斷簽一天從頭開始(sql語(yǔ)句)

1，創(chuàng)建測(cè)試表

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，插入測(cè)試數(shù)據(jù)，簽到時(shí)間為5.21號(hào)到6.5號(hào)，可以寫活，但是要寫存儲(chǔ)過(guò)程，我比較懶，重點(diǎn)應(yīng)該是取簽到數(shù)據(jù)的代碼，就是第三點(diǎn)，呵呵

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，查詢連續(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')=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

未斷數(shù)據(jù)

刪掉23號(hào)數(shù)據(jù)，從24號(hào)開始算，連續(xù)簽三天

以上所述是小編給大家介紹的MYSQL實(shí)現(xiàn)連續(xù)簽到功能斷簽一天從頭開始，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

您可能感興趣的文章:

SQLSERVER記錄登錄用戶的登錄時(shí)間(自寫腳本)
mysql如何查詢兩個(gè)日期之間最大的連續(xù)登錄天數(shù)
mysql如何查詢兩個(gè)日期之間最大的連續(xù)登錄天數(shù)
SQL查詢語(yǔ)句求出用戶的連續(xù)登陸天數(shù)

標(biāo)簽：三明林芝宿州內(nèi)江朔州 AXB 咸寧汕頭

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MYSQL實(shí)現(xiàn)連續(xù)簽到功能斷簽一天從頭開始(sql語(yǔ)句)》，本文關(guān)鍵詞 MYSQL,實(shí)現(xiàn),連續(xù),簽到,功能,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。