[MySQL]-->询5几天之内解决在新年发行一个同事的生日过程
2023-09-27 14:27:02 时间
前言:
遇到朋友提问,例如以下:
SELECT * FROM ali_users WHERE DATEDIFF(CAST(CONCAT(DATE_FORMAT(NOW(),'%y'),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) <=1
上面的能够找到今天或者明天过生日的
假设今天是12月31日,这时候来年的1月1日的就找不到了。怎么办?
1。准备測试数据,须要包含跨年的数据
1.1,准备測试数据的SQL
1.2。在数据库命令行运行SQL
2,写出查询SQL
SELECT * FROM ali_users WHERE
/*不跨年的查询*/
DATEDIFF(CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/
DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 4;
2,1,跨年问题分析
由于跨年的时候生日字段通常月份比較小是1月,所以假设利用DATEDIFF来推断要与月份比較大12月来比較得到相差天数在N天之内的话。就须要YEAR(NOW())+1。当年年份+1再加上月份才干与NOW()比較得出真实的相差天数。
打个比方,今天是2013年12月30日,要查询出来5天之内的过生日的同事,那么2014年1月1日2日3日的也在查询范围之内,这个时候就须要CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d')年份加1形成2014-01-01才干与今天的2013-12-30进行比較得出正确的相差天数。
3.2。查询的生日有跨年的
比现在天是2013年12月30日,要查询5天之内过生日的同事,那么就有2013年的12月30日31日过生日的,也有2014年1月1日2日3日过生日的同事。由于今天是8月30日,所以要把Step#2中的SQL的NOW()改成'2013-12-30 00:10:10'来进行測试。SQL整理例如以下:
SELECT * FROM ali_users WHERE
DATEDIFF(CAST(CONCAT(YEAR('2013-12-30 00:10:10'),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT('2013-12-30 00:10:10','%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/
DATEDIFF(CAST(CONCAT(YEAR('2013-12-30 00:10:10')+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT('2013-12-30 00:10:10','%y-%m-%d') AS DATE)) BETWEEN 0 AND 4;
查询的结果应该是从今天8月30日到9月3日之间过生日的同事,包含Licy,Cari,ZhangSan,LiSi,Mark。
运行结果例如以下所看到的。符合预期,例如以下所看到的:
4,总结
这样的相似的场景。涉及到查询多少天之内的数据,以及涉及到跨度范围比較大的查询(比方跨年或者跨部门,跨省、跨国、跨公司等等)。都能够用这个思路来需求解决这个问题的方法。
遇到朋友提问,例如以下:
SELECT * FROM ali_users WHERE DATEDIFF(CAST(CONCAT(DATE_FORMAT(NOW(),'%y'),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) <=1
上面的能够找到今天或者明天过生日的
假设今天是12月31日,这时候来年的1月1日的就找不到了。怎么办?
1。准备測试数据,须要包含跨年的数据
1.1,准备測试数据的SQL
- USE test;
- DROP TABLE IF EXISTS ali_users;
- CREATE TABLE ali_users (username VARCHAR(10),birthday DATE NOT NULL,iphone VARCHAR(16));
- INSERT INTO ali_users SELECT \'MaoYi\',\'1985-09-04\',\'13998786543\' UNION ALL
- SELECT \'LiuEr\',\'1985-08-30\',\'13998786543\' UNION ALL
- SELECT \'ZhangSan\',\'1981-01-01\',\'13998786543\' UNION ALL
- SELECT \'LiSi\',\'1983-01-02\',\'13998786543\' UNION ALL
- SELECT \'WangWu\',\'1984-11-01\',\'13998786543\' UNION ALL
- SELECT \'ZhaoLiu\',\'1984-11-01\',\'13998786543\' UNION ALL
- SELECT \'SongQi\',\'1986-08-31\',\'13998786543\' UNION ALL
- SELECT \'HuangBa\',\'1989-09-01\',\'13998786543\' UNION ALL
- SELECT \'ZengJiu\',\'1989-09-02\',\'13998786543\' UNION ALL
- SELECT \'LuoShi\',\'1985-09-03\',\'13998786543\' UNION ALL
- SELECT \'Tom\',\'1995-09-05\',\'13998786543\' UNION ALL
- SELECT \'Licy\',\'1991-12-30\',\'13998286543\' UNION ALL
- SELECT \'Cari\',\'1992-12-31\',\'13998286543\' UNION ALL
- SELECT \'Mark\',\'1992-01-03\',\'13998286543\' UNION ALL
- SELECT \'Ruby\',\'1992-01-04\',\'13998286547\';
1.2。在数据库命令行运行SQL
- mysql> USE test;
- DATABASE CHANGED
- mysql> DROP TABLE IF EXISTS ali_users;
- QUERY OK, 0 ROWS affected (0.00 sec)
- mysql> CREATE TABLE ali_users (username VARCHAR(10),birthday DATE NOT NULL,iphone VARCHAR(16));
- QUERY OK, 0 ROWS affected (0.01 sec)
- mysql> INSERT INTO ali_users SELECT \'MaoYi\',\'1985-09-04\',\'13998786543\' UNION ALL
- -> SELECT \'LiuEr\',\'1985-08-30\',\'13998786543\' UNION ALL
- -> SELECT \'ZhangSan\',\'1981-01-01\',\'13998786543\' UNION ALL
- -> SELECT \'LiSi\',\'1983-01-02\',\'13998786543\' UNION ALL
- -> SELECT \'WangWu\',\'1984-11-01\',\'13998786543\' UNION ALL
- -> SELECT \'ZhaoLiu\',\'1984-11-01\',\'13998786543\' UNION ALL
- -> SELECT \'SongQi\',\'1986-08-31\',\'13998786543\' UNION ALL
- -> SELECT \'HuangBa\',\'1989-09-01\',\'13998786543\' UNION ALL
- -> SELECT \'ZengJiu\',\'1989-09-02\',\'13998786543\' UNION ALL
- -> SELECT \'LuoShi\',\'1985-09-03\',\'13998786543\' UNION ALL
- -> SELECT \'Tom\',\'1995-09-05\',\'13998786543\' UNION ALL
- -> SELECT \'Licy\',\'1991-12-30\',\'13998286543\' UNION ALL
- -> SELECT \'Cari\',\'1992-12-31\',\'13998286543\' UNION ALL
- -> SELECT \'Mark\',\'1992-01-03\',\'13998286543\' UNION ALL
- -> SELECT \'Ruby\',\'1992-01-04\',\'13998286547\';
- QUERY OK, 15 ROWS affected (0.01 sec)
- Records: 15 Duplicates: 0 WARNINGS: 0
- mysql> SELECT * FROM ali_users;
- +----------+------------+-------------+
- | username | birthday | iphone |
- +----------+------------+-------------+
- | MaoYi | 1985-09-04 | 13998786543 |
- | LiuEr | 1985-08-30 | 13998786543 |
- | ZhangSan | 1981-01-01 | 13998786543 |
- | LiSi | 1983-01-02 | 13998786543 |
- | WangWu | 1984-11-01 | 13998786543 |
- | ZhaoLiu | 1984-11-01 | 13998786543 |
- | SongQi | 1986-08-31 | 13998786543 |
- | HuangBa | 1989-09-01 | 13998786543 |
- | ZengJiu | 1989-09-02 | 13998786543 |
- | LuoShi | 1985-09-03 | 13998786543 |
- | Tom | 1995-09-05 | 13998786543 |
- | Licy | 1991-12-30 | 13998286543 |
- | Cari | 1992-12-31 | 13998286543 |
- | Mark | 1992-01-03 | 13998286543 |
- | Ruby | 1992-01-04 | 13998286547 |
- +----------+------------+-------------+
- 15 ROWS IN SET (0.00 sec)
- mysql>
2,写出查询SQL
SELECT * FROM ali_users WHERE
/*不跨年的查询*/
DATEDIFF(CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/
DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 4;
2,1,跨年问题分析
由于跨年的时候生日字段通常月份比較小是1月,所以假设利用DATEDIFF来推断要与月份比較大12月来比較得到相差天数在N天之内的话。就须要YEAR(NOW())+1。当年年份+1再加上月份才干与NOW()比較得出真实的相差天数。
打个比方,今天是2013年12月30日,要查询出来5天之内的过生日的同事,那么2014年1月1日2日3日的也在查询范围之内,这个时候就须要CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d')年份加1形成2014-01-01才干与今天的2013-12-30进行比較得出正确的相差天数。
2.2。5天之内的设定
N天之内。用 BETWEEN 0 AND N 来推断,假设是5天之内(包含今天)那么N值就是4,就是 BETWEEN 0 AND 4
3,验证数据
比方提醒近期5天之内(包含今日)过生日的同事,生日快乐。
3.1,查询的数据都在今年之内的,比现在天是8月30日。那么须要运行的SQL例如以下:
SELECT * FROM ali_users WHERE
DATEDIFF(CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/
DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT(NOW(),'%y-%m-%d') AS DATE)) BETWEEN 0 AND 4;
查询的结果应该是从今天8月30日到9月3日之间过生日的同事,包含LiuEr,SongQi,HuangBa,ZengJiu,LuoShi;
运行例如以下所看到的,符合预期,例如以下所看到的:
- mysql> SELECT * FROM ali_users WHERE
- -> DATEDIFF(CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(NOW(),\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- -> OR/* or后面的是捎带解决跨年问题*/
- -> DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(NOW(),\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- -> ;
- +----------+------------+-------------+
- | username | birthday | iphone |
- +----------+------------+-------------+
- | LiuEr | 1985-08-30 | 13998786543 |
- | SongQi | 1986-08-31 | 13998786543 |
- | HuangBa | 1989-09-01 | 13998786543 |
- | ZengJiu | 1989-09-02 | 13998786543 |
- | LuoShi | 1985-09-03 | 13998786543 |
- +----------+------------+-------------+
- 5 ROWS IN SET (0.00 sec)
- mysql>
3.2。查询的生日有跨年的
比现在天是2013年12月30日,要查询5天之内过生日的同事,那么就有2013年的12月30日31日过生日的,也有2014年1月1日2日3日过生日的同事。由于今天是8月30日,所以要把Step#2中的SQL的NOW()改成'2013-12-30 00:10:10'来进行測试。SQL整理例如以下:
SELECT * FROM ali_users WHERE
DATEDIFF(CAST(CONCAT(YEAR('2013-12-30 00:10:10'),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT('2013-12-30 00:10:10','%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/
DATEDIFF(CAST(CONCAT(YEAR('2013-12-30 00:10:10')+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT('2013-12-30 00:10:10','%y-%m-%d') AS DATE)) BETWEEN 0 AND 4;
查询的结果应该是从今天8月30日到9月3日之间过生日的同事,包含Licy,Cari,ZhangSan,LiSi,Mark。
运行结果例如以下所看到的。符合预期,例如以下所看到的:
- mysql> SELECT * FROM ali_users WHERE
- -> DATEDIFF(CAST(CONCAT(YEAR(\'2013-12-30 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2013-12-30 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- -> OR/* or后面的是捎带解决跨年问题*/
- -> DATEDIFF(CAST(CONCAT(YEAR(\'2013-12-30 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2013-12-30 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- -> ;
- +----------+------------+-------------+
- | username | birthday | iphone |
- +----------+------------+-------------+
- | ZhangSan | 1981-01-01 | 13998786543 |
- | LiSi | 1983-01-02 | 13998786543 |
- | Licy | 1991-12-30 | 13998286543 |
- | Cari | 1992-12-31 | 13998286543 |
- | Mark | 1992-01-03 | 13998286543 |
- +----------+------------+-------------+
- 5 ROWS IN SET (0.00 sec)
- mysql>
4,总结
这样的相似的场景。涉及到查询多少天之内的数据,以及涉及到跨度范围比較大的查询(比方跨年或者跨部门,跨省、跨国、跨公司等等)。都能够用这个思路来需求解决这个问题的方法。
版权声明:本文博主原创文章,博客,未经同意不得转载。
相关文章
- Linux下mysql编译安装
- 【MySQL从入门到精通】【高级篇】(二十)适合创建索引的11种情况&不适合创建索引的7种情况,万字长文
- 【MySQL从入门到精通】【高级篇】(十九)索引的分类&创建索引的三种方式&删除索引的两种方式
- mysql-删除日志文件命令详解
- keepalived+mysql backup服务器可ping通过vip但telnet vip+3306失败问题
- 查看服务器中是否安装mysql&服务正在running&查看服务版本
- mysql 高性能搭建0-1 虚拟机中的centos 7.5安装mysql 5.7
- MySQL从零开始
- Mysql:UNDO Tablespaces && UNDO Logs
- centos7最小版本安装nginx+tomcat+java+mysql运行环境
- MySQL服务器端&客户端常见错误
- 【MySQL】MySQL-主从复制-集群方案-数据一致性问题解决方案 && MySQL备份的各种姿势
- MySQL<表单&集合查询>
- Mysql锁机制--乐观锁 & 悲观锁 mysql系列
- 【MySQL数据库基本操作(四)--往MySQL中建数据库、建表、插入数据,直接 查表&查数据】
- MySQL表的高级增删改查
- 【云原生】阿里云ACK部署MySQL & 数据持久化存储
- can't connect to mysql server on localhost <10061>
- mysql:新手N+1查询&连接查询
- c++连接mysql并提示“无法解析的外部符号 _mysql_server_init@12”解决方法&提示缺少“libmysql.dll”
- 68.网络安全渗透测试—[SQL注入篇7]—[MySQL+PHP-报错注入详解&示例]
- MySQL存储过程&函数&触发器&事件大一统
- mysql---order by
- MYSQL错误解决:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- 使用Docker安装mysql,挂载外部配置和数据