当前位置: 开发笔记 > 后端 > 正文

[MySQL]--)查询5天之内过生日的同事中的跨年问题的解决过程_MySQL

作者：0o可人儿o0_962 | 来源：互联网 | 2018-04-17 22:30

前言：

前言：

遇到朋友提问，如下：

SELECT * FROM ali_users WHERE DATEDIFF(CAST(CONCAT(DATE_FORMAT(NOW(),&＃39;%y&＃39;),DATE_FORMAT(birthday,&＃39;-%m-%d&＃39;))AS DATE),CAST(DATE_FORMAT(NOW(),&＃39;%y-%m-%d&＃39;) AS DATE)) <=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 \&＃39;MaoYi\&＃39;,\&＃39;1985-09-04\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;LiuEr\&＃39;,\&＃39;1985-08-30\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;ZhangSan\&＃39;,\&＃39;1981-01-01\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;LiSi\&＃39;,\&＃39;1983-01-02\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;WangWu\&＃39;,\&＃39;1984-11-01\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;ZhaoLiu\&＃39;,\&＃39;1984-11-01\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;SongQi\&＃39;,\&＃39;1986-08-31\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;HuangBa\&＃39;,\&＃39;1989-09-01\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;ZengJiu\&＃39;,\&＃39;1989-09-02\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;LuoShi\&＃39;,\&＃39;1985-09-03\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;Tom\&＃39;,\&＃39;1995-09-05\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
SELECT \&＃39;Licy\&＃39;,\&＃39;1991-12-30\&＃39;,\&＃39;13998286543\&＃39; UNION ALL
SELECT \&＃39;Cari\&＃39;,\&＃39;1992-12-31\&＃39;,\&＃39;13998286543\&＃39; UNION ALL
SELECT \&＃39;Mark\&＃39;,\&＃39;1992-01-03\&＃39;,\&＃39;13998286543\&＃39; UNION ALL
SELECT \&＃39;Ruby\&＃39;,\&＃39;1992-01-04\&＃39;,\&＃39;13998286547\&＃39;;
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 \&＃39;MaoYi\&＃39;,\&＃39;1985-09-04\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;LiuEr\&＃39;,\&＃39;1985-08-30\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;ZhangSan\&＃39;,\&＃39;1981-01-01\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;LiSi\&＃39;,\&＃39;1983-01-02\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;WangWu\&＃39;,\&＃39;1984-11-01\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;ZhaoLiu\&＃39;,\&＃39;1984-11-01\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;SongQi\&＃39;,\&＃39;1986-08-31\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;HuangBa\&＃39;,\&＃39;1989-09-01\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;ZengJiu\&＃39;,\&＃39;1989-09-02\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;LuoShi\&＃39;,\&＃39;1985-09-03\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;Tom\&＃39;,\&＃39;1995-09-05\&＃39;,\&＃39;13998786543\&＃39; UNION ALL
-> SELECT \&＃39;Licy\&＃39;,\&＃39;1991-12-30\&＃39;,\&＃39;13998286543\&＃39; UNION ALL
-> SELECT \&＃39;Cari\&＃39;,\&＃39;1992-12-31\&＃39;,\&＃39;13998286543\&＃39; UNION ALL
-> SELECT \&＃39;Mark\&＃39;,\&＃39;1992-01-03\&＃39;,\&＃39;13998286543\&＃39; UNION ALL
-> SELECT \&＃39;Ruby\&＃39;,\&＃39;1992-01-04\&＃39;,\&＃39;13998286547\&＃39;;
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
2,1，跨年问题分析
因为跨年的时候生日字段通常月份比较小是1月，所以如果利用DATEDIFF来判断要与月份比较大12月来比较得到相差天数在N天之内的话，就需要YEAR(NOW())+1，当年年份+1再加上月份才能与NOW()比较得出真实的相差天数。
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
查询的结果应该是从今天8月30日到9月3日之间过生日的同事，包括LiuEr,SongQi,HuangBa,ZengJiu,LuoShi;

mysql> SELECT * FROM ali_users WHERE
-> DATEDIFF(CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,\&＃39;-%m-%d\&＃39;))AS DATE),CAST(DATE_FORMAT(NOW(),\&＃39;%y-%m-%d\&＃39;) AS DATE)) BETWEEN 0 AND 4
-> OR/* or后面的是捎带解决跨年问题*/
-> DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,\&＃39;-%m-%d\&＃39;))AS DATE),CAST(DATE_FORMAT(NOW(),\&＃39;%y-%m-%d\&＃39;) 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()改成&＃39;2013-12-30 00:10:10&＃39;来进行测试，SQL整理如下：
mysql> SELECT * FROM ali_users WHERE
-> DATEDIFF(CAST(CONCAT(YEAR(\&＃39;2013-12-30 00:10:10\&＃39;),DATE_FORMAT(birthday,\&＃39;-%m-%d\&＃39;))AS DATE),CAST(DATE_FORMAT(\&＃39;2013-12-30 00:10:10\&＃39;,\&＃39;%y-%m-%d\&＃39;) AS DATE)) BETWEEN 0 AND 4
-> OR/* or后面的是捎带解决跨年问题*/
-> DATEDIFF(CAST(CONCAT(YEAR(\&＃39;2013-12-30 00:10:10\&＃39;)+1,DATE_FORMAT(birthday,\&＃39;-%m-%d\&＃39;))AS DATE),CAST(DATE_FORMAT(\&＃39;2013-12-30 00:10:10\&＃39;,\&＃39;%y-%m-%d\&＃39;) 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，总结

推荐阅读

http
推荐一个ASP的内容管理框架（ASP Nuke）的优势和适用场景

本文推荐了一个ASP的内容管理框架ASP Nuke，并介绍了其主要功能和特点。ASP Nuke支持文章新闻管理、投票、论坛等主要内容，并可以自定义模块。最新版本为0.8，虽然目前仍处于Alpha状态，但作者表示会继续更新完善。文章还分析了使用ASP的原因，包括ASP相对较小、易于部署和较简单等优势，适用于建立门户、网站的组织和小公司等场景。 ... [详细]

蜡笔小新 2023-12-14 18:11:11
php
Android 新闻App的本地服务器搭建教程

本文介绍了在开发Android新闻App时，搭建本地服务器的步骤。通过使用XAMPP软件，可以一键式搭建起开发环境，包括Apache、MySQL、PHP、PERL。在本地服务器上新建数据库和表，并设置相应的属性。最后，给出了创建new表的SQL语句。这个教程适合初学者参考。 ... [详细]

蜡笔小新 2023-12-14 17:15:19
php
如何在MySQL中将零值替换为先前的非零值？

本文介绍了如何在MySQL中将零值替换为先前的非零值的方法，包括使用内联查询和更新查询。同时还提供了选择正确值的方法。 ... [详细]

蜡笔小新 2023-12-14 16:59:24
php
mysql分组排序_在MySQL中实现分组排序功能

在数据分析工作中，我们通常会遇到这样的问题，一个业务部门由若干业务组构成，需要筛选出每个业务组里业绩前N名的业务员。这其实是一个分组排序的 ... [详细]

蜡笔小新 2023-12-14 14:41:26
cookie
如何限制php数据库链接数和连接超时时间？

本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目，以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数，以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ... [详细]

蜡笔小新 2023-12-14 14:06:10
http
Oracle Database 10g许可授予信息及高级功能详解

本文介绍了Oracle Database 10g许可授予信息及其中的高级功能，包括数据库优化数据包、SQL访问指导、SQL优化指导、SQL优化集和重组对象。同时提供了详细说明，指导用户在Oracle Database 10g中如何使用这些功能。 ... [详细]

蜡笔小新 2023-12-14 13:12:10
php
Hibernate基础映射

在说Hibernate映射前，我们先来了解下对象关系映射ORM。ORM的实现思想就是将关系数据库中表的数据映射成对象，以对象的形式展现。这样开发人员就可以把对数据库的操作转化为对 ... [详细]

蜡笔小新 2023-12-14 10:57:47
php
MysqlDump_mysqldump全库备份相关知识详解

本文详细介绍了MysqlDump和mysqldump进行全库备份的相关知识，包括备份命令的使用方法、my.cnf配置文件的设置、binlog日志的位置指定、增量恢复的方式以及适用于innodb引擎和myisam引擎的备份方法。对于需要进行数据库备份的用户来说，本文提供了一些有价值的参考内容。 ... [详细]

蜡笔小新 2023-12-14 10:03:27
php
PHP中的MySQL函数库及其常用函数介绍

本文由编程笔记小编整理，介绍了PHP中的MySQL函数库及其常用函数，包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-14 08:19:53
spring
Spring特性实现接口多类的动态调用详解

本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍，以及getBeansOfType方法的应用，解决了在实际工作中遇到的接口及多个实现类的问题。同时，文章还提到了SPI使用的不便之处，并介绍了借助ApplicationContext实现需求的方法。阅读本文，你将了解到Spring特性的实现原理和实际应用方式。 ... [详细]

蜡笔小新 2023-12-14 03:24:19
spring
如何查看mysql的安装路径

本文介绍了通过mysql命令查看mysql的安装路径的方法，提供了相应的sql语句，并希望对读者有参考价值。 ... [详细]

蜡笔小新 2023-12-13 13:23:09
ci
mysql-cluster集群sql节点高可用keepalived的故障处理过程

本文描述了mysql-cluster集群sql节点高可用keepalived的故障处理过程，包括故障发生时间、故障描述、故障分析等内容。根据keepalived的日志分析，发现bogus VRRP packet received on eth0 !!!等错误信息，进而导致vip地址失效，使得mysql-cluster的api无法访问。针对这个问题，本文提供了相应的解决方案。 ... [详细]

蜡笔小新 2023-12-12 19:20:50
http
POCO C++ Libraies介绍及常见用法

POCOCLibraies属于功能广泛、轻量级别的开源框架库，它拥有媲美Boost库的功能以及较小的体积广泛应用在物联网平台、工业自动化等领域。POCOCLibrai ... [详细]

蜡笔小新 2023-10-17 18:56:35
http
MySQL表分区的创建、增加和删除方法详解

本文详细介绍了MySQL表分区的创建、增加和删除方法，包括查看分区数据量和全库数据量的方法。欢迎大家阅读并给予点评。 ... [详细]

蜡笔小新 2023-12-13 12:26:11
http
C#制作Java+Mysql+Tomcat环境安装程序，一键式安装教程

本文介绍了如何使用C#制作Java+Mysql+Tomcat环境安装程序，实现一键式安装。通过将JDK、Mysql、Tomcat三者制作成一个安装包，解决了客户在安装软件时的复杂配置和繁琐问题，便于管理软件版本和系统集成。具体步骤包括配置JDK环境变量和安装Mysql服务，其中使用了MySQL Server 5.5社区版和my.ini文件。安装方法为通过命令行将目录转到mysql的bin目录下，执行mysqld --install MySQL5命令。 ... [详细]

蜡笔小新 2023-12-12 19:29:55

0o可人儿o0_962

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章