乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當(dāng)前位置： 維易PHP培訓(xùn)學(xué)院 > MYSQL教程 > 內(nèi)容正文

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

作者：VEPHP   時(shí)間 2017-09-14

《MYSQL教程MYSQL實(shí)現(xiàn)連續(xù)簽到功能斷簽一天從頭開始(sql語(yǔ)句)》要點(diǎn)：
本文介紹了MYSQL教程MYSQL實(shí)現(xiàn)連續(xù)簽到功能斷簽一天從頭開始(sql語(yǔ)句)，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

MYSQL應(yīng)用1,創(chuàng)建測(cè)試表

MYSQL應(yī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

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

MYSQL應(yīng)用
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');

MYSQL應(yīng)用3,查詢連續(xù)簽到數(shù)據(jù)

MYSQL應(yīng)用
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

MYSQL應(yīng)用未斷數(shù)據(jù)

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

MYSQL應(yīng)用刪掉23號(hào)數(shù)據(jù),從24號(hào)開始算,連續(xù)簽三天

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

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

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/1045.html


    標(biāo)簽：