[MySQL] 查詢5天之內過生日的同事中的跨年問題以及閏年2月29日問題的解決過程
前言:
遇到朋友提問,如下:
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 ;
儲存函式執行如下圖所示:
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/29734436/viewspace-1262018/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【mysql】配置MySQL,解決安裝過程中的問題MySql
- Mysql安裝過程中遇到的問題及解決辦法MySql
- 在CSS中解決內容過長的問題CSS
- Android開發過程中遇到的問題以及解決辦法 how toAndroid
- HBase一次慢查詢請求的問題排查與解決過程
- IT中的閏秒問題
- munium學習過程中問題解決
- Oracle RAC之--安裝過程中碰到的問題及解決方法Oracle
- Data Pump Import速度問題之解決過程Import
- 死鎖問題排查過程-間隙鎖的復現以及解決
- 閏年的計算問題
- MySQL儲存過程中的sql_mode問題MySql儲存過程
- 一次線上問題的排查解決過程
- 爬蟲過程中遇到的問題爬蟲
- 安裝pygame和pip的問題以及過程GAM
- PYTORCH安裝過程以及遇到的問題PyTorch
- sphinx查詢過濾問題
- mysql多次呼叫儲存過程的問題MySql儲存過程
- 一個lua問題解決過程
- 過多if - else 的問題, 以及策略模式 + 反射解決方法模式反射
- 關於 mysql 中的 rand () 查詢問題MySql
- 無線路由器撥號過程中可能會遇到的問題以及解決方法路由器
- 遊戲,一個有樂趣的、解決問題的過程遊戲
- JN專案-幫同事搭建環境過程中出現的問題
- 開發過程中mysql常見問題MySql
- Cacti監控MySQL實現過程中碰到的問題解彙總MySql
- Composer 使用過程中遇到的問題和解決方案
- 運維必備-解決鎖問題的全過程運維
- 記一次 Composer 問題的解決過程!!
- 解決儲存過程擷取錯誤的問題儲存過程
- 安裝yeoman,grunt過程中的問題
- 學習vue過程中遇到的問題Vue
- STATSPACK安裝過程中遇到的問題
- MySQL儲存過程的許可權問題MySql儲存過程
- 解決Java執行過程中拋簽名異常的問題Java
- elk(單機)安裝過程中遇到的問題及解決方法
- 你遇到過的相容性問題以及解決辦法
- Mysql中的巢狀子查詢問題QSBSMySql巢狀