前言：

遇到朋友提問，如下：

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

1，準備測試數(shù)據(jù)，需要包含跨年的數(shù)據(jù)

1.1，準備測試數(shù)據(jù)的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，在數(shù)據(jù)庫命令行執(zhí)行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
2,1，跨年問題分析
因為跨年的時候生日字段通常月份比較小是1月，所以如果利用DATEDIFF來判斷要與月份比較大12月來比較得到相差天數(shù)在N天之內(nèi)的話，就需要YEAR(NOW())+1，當年年份+1再加上月份才能與NOW()比較得出真實的相差天數(shù)。
2.2，5天之內(nèi)的設定
N天之內(nèi)，用 BETWEEN 0 AND N 來判斷，如果是5天之內(nèi)(包含今天)那么N值就是4，就是 BETWEEN 0 AND 4
3，驗證數(shù)據(jù)
比如提醒最近5天之內(nèi)(包括今日)過生日的同事，生日快樂。
3.1，查詢的數(shù)據(jù)都在今年之內(nèi)的，比如今天是8月30日，那么需要執(zhí)行的SQL如下：
SELECT * FROM ali_users WHERE
查詢的結(jié)果應該是從今天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天之內(nèi)過生日的同事，那么就有2013年的12月30日31日過生日的，也有2014年1月1日2日3日過生日的同事，因為今天是8月30日，所以要把Step#2中的SQL的NOW()改成'2013-12-30 00:10:10'來進行測試，SQL整理如下：
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，總結(jié)

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com