前言:
遇到朋友提问,如下:
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进行比较得出正确的相差天数。
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;
执行如下所示,符合预期,如下所示:
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。
执行结果如下所示,符合预期,如下所示:
5,补充闰年判断
有朋友提醒,闰年2月29日生日的话,可能查询不到,想到确实没有考虑到这个特殊的日期。
5.1,准备测试数据SQL,包含1980-02-29这一天生日的朋友。
INSERT INTO ali_users SELECT 'Jeff','1980-02-29','13998786549'
UNION ALL SELECT 'WeiYa','1980-02-28','13998786549'
UNION ALL SELECT 'XiaoTeng','1980-03-01','13998786549'
UNION ALL SELECT 'HeSheng','1980-03-02','13998786549'
UNION ALL SELECT 'JingPan','1980-03-03','13998786549'
UNION ALL SELECT 'WuHong','1986-03-04','13998786549';
5.2,录入测试数据
5.3,执行原来的旧版本的SQL查询检查结果
把Step#2中的SQL的NOW()改成'2014-02-28 00:10:10'来进行测试,确实没有包括2月29日生日的同事,如下所示:
5.4,先建立一个存储函数f_isleap_year判断当年年份是否是闰年
5.4.1 准备判断是否闰年的SQL
存储函数执行如下图所示:
5.4.2 准备SQL语句
5.4.3 在非闰年的时候,验证闰年2月29日生日,选择2014年非闰年测试
把Step#2中的SQL的NOW()改成'2014-02-28 00:10:10'来进行测试,SQL如下所示:
SELECT * FROM ali_users WHERE
DATEDIFF(CAST(CONCAT(YEAR('2014-02-28 00:10:10'),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT('2014-02-28 00:10:10','%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/
DATEDIFF(CAST(CONCAT(YEAR('2014-02-28 00:10:10')+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT('2014-02-28 00:10:10','%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR /*补充闰年2月29日的生日方法*/
(
f_not_leap_year(YEAR('2014-02-28 00:10:10'))
AND DATE_FORMAT(birthday,'-%m-%d')='-02-29'
AND DATEDIFF(CAST(CONCAT('2000',DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(CONCAT('2000',DATE_FORMAT('2000-02-28 00:10:10','-%m-%d')) AS DATE))
BETWEEN 0 AND 4
);
执行SQL检验成果,如果当天是2014-02-28,看到已经有2月29日的生日的同事被记录进来了,其实包含了2月28日、2月29日、3月1日、3月2日、3月3日、3月4日的生日的同事。
PS:因为2月29日在当年不存在,所以不算这5天之内的范畴,执行结果如下所示:
5.4.4 在闰年的时候,验证闰年2月29日生日,选择2004年闰年测试
把Step#2中的SQL的NOW()改成'2004-02-28 00:10:10'来进行测试,SQL如下所示:
执行SQL检验成果,如果当天是2004-02-28,看到已经有2月29日的生日的同事被记录进来了,其实包含了2月28日、2月29日、3月1日、3月2日、3月3日的生日的同事。
PS:因为2月29日在当年存在,所以算这5天之内的范畴,执行结果如下所示:
6,总结
这种类似的场景,涉及到查询多少天之内的数据,以及涉及到跨度范围比较大的查询(比如跨年或者跨部门,跨省、跨国、跨公司等等),都可以用这个思路来需求解决问题的方法。
遇到朋友提问,如下:
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>
5,补充闰年判断
有朋友提醒,闰年2月29日生日的话,可能查询不到,想到确实没有考虑到这个特殊的日期。
5.1,准备测试数据SQL,包含1980-02-29这一天生日的朋友。
INSERT INTO ali_users SELECT 'Jeff','1980-02-29','13998786549'
UNION ALL SELECT 'WeiYa','1980-02-28','13998786549'
UNION ALL SELECT 'XiaoTeng','1980-03-01','13998786549'
UNION ALL SELECT 'HeSheng','1980-03-02','13998786549'
UNION ALL SELECT 'JingPan','1980-03-03','13998786549'
UNION ALL SELECT 'WuHong','1986-03-04','13998786549';
5.2,录入测试数据
- mysql> use test;
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
-
- Database changed
- mysql> INSERT INTO ali_users SELECT \'Jeff\',\'1980-02-29\',\'13998786549\' UNION ALL SELECT \'XiaoTeng\',\'1980-03-01\',\'13998786549\'
- -> UNION ALL SELECT \'HeSheng\',\'1980-03-02\',\'13998786549\'
- -> UNION ALL SELECT \'JingPan\',\'1980-03-03\',\'13998786549\'
- -> UNION ALL SELECT \'WuHong\',\'1986-03-04\',\'13998786549\';
- Query OK, 5 rows affected (0.00 sec)
- Records: 5 Duplicates: 0 Warnings: 0
-
- mysql>
5.3,执行原来的旧版本的SQL查询检查结果
把Step#2中的SQL的NOW()改成'2014-02-28 00:10:10'来进行测试,确实没有包括2月29日生日的同事,如下所示:
- mysql> SELECT * FROM ali_users WHERE
- -> DATEDIFF(CAST(CONCAT(YEAR(\'2014-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2014-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- -> OR/* or后面的是捎带解决跨年问题*/
- -> DATEDIFF(CAST(CONCAT(YEAR(\'2014-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2014-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- -> ;
- +----------+------------+-------------+
- | username | birthday | iphone |
- +----------+------------+-------------+
- | XiaoTeng | 1980-03-01 | 13998786549 |
- | HeSheng | 1980-03-02 | 13998786549 |
- | JingPan | 1980-03-03 | 13998786549 |
- | WuHong | 1986-03-04 | 13998786549 |
- +----------+------------+-------------+
- 4 rows in set, 2 warnings (0.00 sec)
-
-
- mysql>
5.4,先建立一个存储函数f_isleap_year判断当年年份是否是闰年
5.4.1 准备判断是否闰年的SQL
- DELIMITER $$
- USE `test`$$
- DROP FUNCTION IF EXISTS `f_not_leap_year`$$
- CREATE FUNCTION `f_not_leap_year`(p_year BIGINT) RETURNS BOOLEAN
- BEGIN
- /*是闰年则返回0(false),不是闰年则返回1(true)*/
- DECLARE v_flag INT DEFAULT 0;
- /*①、普通年能被4整除且不能被100整除的为闰年。(如2004年就是闰年,1901年不是闰年)*/
- IF (p_year%4)=0 AND (p_year%100)>0 THEN
- SET v_flag=0;
- /*②、世纪年能被400整除的是闰年。(如2000年是闰年,1900年不是闰年) */
- ELSEIF (p_year%400)=0 THEN
- SET v_flag=0;
- /*③、对于数值很大的年份,这年如果能整除3200,并且能整除172800则是闰年。如172800年是闰年,
- 86400年不是闰年(因为虽然能整除3200,但不能整除172800)(此按一回归年365天5h48\'45.5\'\'计算)。
- */
- ELSEIF (p_year%3200)=0 AND (p_year%172800)=0 THEN
- SET v_flag=0;
- ELSE
- SET v_flag=1;
- END IF;
- RETURN v_flag;
- END$$
- DELIMITER ;
存储函数执行如下图所示:
![](http://img.blog.itpub.net/blog/attachment/201409/1/26230597_1409533912FUTv.jpg?x-oss-process=style/bb)
5.4.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
- OR /*补充闰年2月29日的生日问题*/
- (
- f_not_leap_year(YEAR(NOW()))
- AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
- AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(NOW(),\'-%m-%d\')) AS DATE))
- BETWEEN 0 AND 4
- );
5.4.3 在非闰年的时候,验证闰年2月29日生日,选择2014年非闰年测试
把Step#2中的SQL的NOW()改成'2014-02-28 00:10:10'来进行测试,SQL如下所示:
SELECT * FROM ali_users WHERE
DATEDIFF(CAST(CONCAT(YEAR('2014-02-28 00:10:10'),DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT('2014-02-28 00:10:10','%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/
DATEDIFF(CAST(CONCAT(YEAR('2014-02-28 00:10:10')+1,DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(DATE_FORMAT('2014-02-28 00:10:10','%y-%m-%d') AS DATE)) BETWEEN 0 AND 4
OR /*补充闰年2月29日的生日方法*/
(
f_not_leap_year(YEAR('2014-02-28 00:10:10'))
AND DATE_FORMAT(birthday,'-%m-%d')='-02-29'
AND DATEDIFF(CAST(CONCAT('2000',DATE_FORMAT(birthday,'-%m-%d'))AS DATE),CAST(CONCAT('2000',DATE_FORMAT('2000-02-28 00:10:10','-%m-%d')) AS DATE))
BETWEEN 0 AND 4
);
执行SQL检验成果,如果当天是2014-02-28,看到已经有2月29日的生日的同事被记录进来了,其实包含了2月28日、2月29日、3月1日、3月2日、3月3日、3月4日的生日的同事。
PS:因为2月29日在当年不存在,所以不算这5天之内的范畴,执行结果如下所示:
- mysql> SELECT * FROM ali_users WHERE
- -> DATEDIFF(CAST(CONCAT(YEAR(\'2014-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2014-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- -> OR/* or后面的是捎带解决跨年问题*/
- -> DATEDIFF(CAST(CONCAT(YEAR(\'2014-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2014-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- -> OR /*补充闰年2月29日的生日方法*/
- -> (
- -> f_not_leap_year(YEAR(\'2014-02-28 00:10:10\'))
- -> AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
- -> AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(\'2000-02-28 00:10:10\',\'-%m-%d\')) AS DATE))
- -> BETWEEN 0 AND 4
- -> );
- +----------+------------+-------------+
- | username | birthday | iphone |
- +----------+------------+-------------+
- | Jeff | 1980-02-29 | 13998786549 |
- | XiaoTeng | 1980-03-01 | 13998786549 |
- | HeSheng | 1980-03-02 | 13998786549 |
- | JingPan | 1980-03-03 | 13998786549 |
- | WuHong | 1986-03-04 | 13998786549 |
- | WeiYa | 1980-02-28 | 13998786549 |
- +----------+------------+-------------+
- 6 rows in set, 2 warnings (0.00 sec)
-
-
- mysql>
5.4.4 在闰年的时候,验证闰年2月29日生日,选择2004年闰年测试
把Step#2中的SQL的NOW()改成'2004-02-28 00:10:10'来进行测试,SQL如下所示:
- SELECT * FROM ali_users WHERE
- DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- OR/* or后面的是捎带解决跨年问题*/
- DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- OR /*补充闰年2月29日的生日方法*/
- (
- f_not_leap_year(YEAR(\'2004-02-28 00:10:10\'))
- AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
- AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(\'2004-02-28 00:10:10\',\'-%m-%d\')) AS DATE))
- BETWEEN 0 AND 4
- );
执行SQL检验成果,如果当天是2004-02-28,看到已经有2月29日的生日的同事被记录进来了,其实包含了2月28日、2月29日、3月1日、3月2日、3月3日的生日的同事。
PS:因为2月29日在当年存在,所以算这5天之内的范畴,执行结果如下所示:
- mysql> SELECT * FROM ali_users WHERE
- -> DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\'),DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- -> OR/* or后面的是捎带解决跨年问题*/
- -> DATEDIFF(CAST(CONCAT(YEAR(\'2004-02-28 00:10:10\')+1,DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(DATE_FORMAT(\'2004-02-28 00:10:10\',\'%y-%m-%d\') AS DATE)) BETWEEN 0 AND 4
- -> OR /*补充闰年2月29日的生日方法*/
- -> (
- -> f_not_leap_year(YEAR(\'2004-02-28 00:10:10\'))
- -> AND DATE_FORMAT(birthday,\'-%m-%d\')=\'-02-29\'
- -> AND DATEDIFF(CAST(CONCAT(\'2000\',DATE_FORMAT(birthday,\'-%m-%d\'))AS DATE),CAST(CONCAT(\'2000\',DATE_FORMAT(\'2004-02-28 00:10:10\',\'-%m-%d\')) AS DATE))
- -> BETWEEN 0 AND 4
- -> );
- +----------+------------+-------------+
- | username | birthday | iphone |
- +----------+------------+-------------+
- | Jeff | 1980-02-29 | 13998786549 |
- | XiaoTeng | 1980-03-01 | 13998786549 |
- | HeSheng | 1980-03-02 | 13998786549 |
- | JingPan | 1980-03-03 | 13998786549 |
- | WeiYa | 1980-02-28 | 13998786549 |
- +----------+------------+-------------+
- 5 rows in set (0.00 sec)
-
-
- mysql>
6,总结
这种类似的场景,涉及到查询多少天之内的数据,以及涉及到跨度范围比较大的查询(比如跨年或者跨部门,跨省、跨国、跨公司等等),都可以用这个思路来需求解决问题的方法。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26230597/viewspace-1260767/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26230597/viewspace-1260767/