MYSQL实现连续签到功能断签一天从头开始(sql语句)
2019/6/30 18:18:42
本文主要是介绍MYSQL实现连续签到功能断签一天从头开始(sql语句),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
1,创建测试表
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表示签到数据,1表示签到日期字典数据' ) ENGINE=InnoDB DEFAULT CHARSET=utf8
2,插入测试数据,签到时间为5.21号到6.5号,可以写活,但是要写存储过程,我比较懒,重点应该是取签到数据的代码,就是第三点,呵呵
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','吴小双签到数据','2017-05-21 00:00:00','0'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-22 00:00:00','0'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-23 00:00:00','0'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-24 00:00:00','0'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-25 00:00:00','0'); insert into `testsign` (`userid`, `username`, `signtime`, `type`) values('800675','吴小双签到数据','2017-05-26 00:00:00','0');
3,查询连续签到数据
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
未断数据
删掉23号数据,从24号开始算,连续签三天
以上所述是小编给大家介绍的MYSQL实现连续签到功能断签一天从头开始,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对找一找教程网网站的支持!
这篇关于MYSQL实现连续签到功能断签一天从头开始(sql语句)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享