首页 > 代码库 > [MySQL]-->查询5天之内过生日的同事中的跨年问题的解决过程

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

前言:
        遇到朋友提问,如下:
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. USE test;
  2. DROP TABLE IF EXISTS ali_users;
  3. CREATE TABLE ali_users (username VARCHAR(10),birthday DATE NOT NULL,iphone VARCHAR(16));
  4. INSERT INTO ali_users SELECT \‘MaoYi\‘,\‘1985-09-04\‘,\‘13998786543\‘ UNION ALL
  5. SELECT \‘LiuEr\‘,\‘1985-08-30\‘,\‘13998786543\‘ UNION ALL
  6. SELECT \‘ZhangSan\‘,\‘1981-01-01\‘,\‘13998786543\‘ UNION ALL
  7. SELECT \‘LiSi\‘,\‘1983-01-02\‘,\‘13998786543\‘ UNION ALL
  8. SELECT \‘WangWu\‘,\‘1984-11-01\‘,\‘13998786543\‘ UNION ALL
  9. SELECT \‘ZhaoLiu\‘,\‘1984-11-01\‘,\‘13998786543\‘ UNION ALL
  10. SELECT \‘SongQi\‘,\‘1986-08-31\‘,\‘13998786543\‘ UNION ALL
  11. SELECT \‘HuangBa\‘,\‘1989-09-01\‘,\‘13998786543\‘ UNION ALL
  12. SELECT \‘ZengJiu\‘,\‘1989-09-02\‘,\‘13998786543\‘ UNION ALL
  13. SELECT \‘LuoShi\‘,\‘1985-09-03\‘,\‘13998786543\‘ UNION ALL
  14. SELECT \‘Tom\‘,\‘1995-09-05\‘,\‘13998786543\‘ UNION ALL
  15. SELECT \‘Licy\‘,\‘1991-12-30\‘,\‘13998286543\‘ UNION ALL
  16. SELECT \‘Cari\‘,\‘1992-12-31\‘,\‘13998286543\‘ UNION ALL
  17. SELECT \‘Mark\‘,\‘1992-01-03\‘,\‘13998286543\‘ UNION ALL
  18. SELECT \‘Ruby\‘,\‘1992-01-04\‘,\‘13998286547\‘;

1.2,在数据库命令行执行SQL

  1. mysql> USE test;
  2. DATABASE CHANGED
  3. mysql> DROP TABLE IF EXISTS ali_users;
  4. QUERY OK, 0 ROWS affected (0.00 sec)

  5. mysql> CREATE TABLE ali_users (username VARCHAR(10),birthday DATE NOT NULL,iphone VARCHAR(16));
  6. QUERY OK, 0 ROWS affected (0.01 sec)

  7. mysql> INSERT INTO ali_users SELECT \‘MaoYi\‘,\‘1985-09-04\‘,\‘13998786543\‘ UNION ALL
  8.     -> SELECT \‘LiuEr\‘,\‘1985-08-30\‘,\‘13998786543\‘ UNION ALL
  9.     -> SELECT \‘ZhangSan\‘,\‘1981-01-01\‘,\‘13998786543\‘ UNION ALL
  10.     -> SELECT \‘LiSi\‘,\‘1983-01-02\‘,\‘13998786543\‘ UNION ALL
  11.     -> SELECT \‘WangWu\‘,\‘1984-11-01\‘,\‘13998786543\‘ UNION ALL
  12.     -> SELECT \‘ZhaoLiu\‘,\‘1984-11-01\‘,\‘13998786543\‘ UNION ALL
  13.     -> SELECT \‘SongQi\‘,\‘1986-08-31\‘,\‘13998786543\‘ UNION ALL
  14.     -> SELECT \‘HuangBa\‘,\‘1989-09-01\‘,\‘13998786543\‘ UNION ALL
  15.     -> SELECT \‘ZengJiu\‘,\‘1989-09-02\‘,\‘13998786543\‘ UNION ALL
  16.     -> SELECT \‘LuoShi\‘,\‘1985-09-03\‘,\‘13998786543\‘ UNION ALL
  17.     -> SELECT \‘Tom\‘,\‘1995-09-05\‘,\‘13998786543\‘ UNION ALL
  18.     -> SELECT \‘Licy\‘,\‘1991-12-30\‘,\‘13998286543\‘ UNION ALL
  19.     -> SELECT \‘Cari\‘,\‘1992-12-31\‘,\‘13998286543\‘ UNION ALL
  20.     -> SELECT \‘Mark\‘,\‘1992-01-03\‘,\‘13998286543\‘ UNION ALL
  21.     -> SELECT \‘Ruby\‘,\‘1992-01-04\‘,\‘13998286547\‘;
  22. QUERY OK, 15 ROWS affected (0.01 sec)
  23. Records: 15 Duplicates: 0 WARNINGS: 0

  24. mysql> SELECT * FROM ali_users;
  25. +----------+------------+-------------+
  26. | username | birthday | iphone |
  27. +----------+------------+-------------+
  28. | MaoYi | 1985-09-04 | 13998786543 |
  29. | LiuEr | 1985-08-30 | 13998786543 |
  30. | ZhangSan | 1981-01-01 | 13998786543 |
  31. | LiSi | 1983-01-02 | 13998786543 |
  32. | WangWu | 1984-11-01 | 13998786543 |
  33. | ZhaoLiu | 1984-11-01 | 13998786543 |
  34. | SongQi | 1986-08-31 | 13998786543 |
  35. | HuangBa | 1989-09-01 | 13998786543 |
  36. | ZengJiu | 1989-09-02 | 13998786543 |
  37. | LuoShi | 1985-09-03 | 13998786543 |
  38. | Tom | 1995-09-05 | 13998786543 |
  39. | Licy | 1991-12-30 | 13998286543 |
  40. | Cari | 1992-12-31 | 13998286543 |
  41. | Mark | 1992-01-03 | 13998286543 |
  42. | Ruby | 1992-01-04 | 13998286547 |
  43. +----------+------------+-------------+
  44. 15 ROWS IN SET (0.00 sec)

  45. 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;
执行如下所示,符合预期,如下所示:


  1. mysql> SELECT * FROM ali_users WHERE 
  2.     -> 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
  3.     -> OR/* or后面的是捎带解决跨年问题*/ 
  4.     -> 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
  5.     -> ;
  6. +----------+------------+-------------+
  7. | username | birthday | iphone |
  8. +----------+------------+-------------+
  9. | LiuEr | 1985-08-30 | 13998786543 |
  10. | SongQi | 1986-08-31 | 13998786543 |
  11. | HuangBa | 1989-09-01 | 13998786543 |
  12. | ZengJiu | 1989-09-02 | 13998786543 |
  13. | LuoShi | 1985-09-03 | 13998786543 |
  14. +----------+------------+-------------+
  15. 5 ROWS IN SET (0.00 sec)

  16. 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。
执行结果如下所示,符合预期,如下所示:

  1. mysql> SELECT * FROM ali_users WHERE 
  2.     -> 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
  3.     -> OR/* or后面的是捎带解决跨年问题*/ 
  4.     -> 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
  5.     -> ;
  6. +----------+------------+-------------+
  7. | username | birthday | iphone |
  8. +----------+------------+-------------+
  9. | ZhangSan | 1981-01-01 | 13998786543 |
  10. | LiSi | 1983-01-02 | 13998786543 |
  11. | Licy | 1991-12-30 | 13998286543 |
  12. | Cari | 1992-12-31 | 13998286543 |
  13. | Mark | 1992-01-03 | 13998286543 |
  14. +----------+------------+-------------+
  15. 5 ROWS IN SET (0.00 sec)

  16. mysql>

4,总结
    这种类似的场景,涉及到查询多少天之内的数据,以及涉及到跨度范围比较大的查询(比如跨年或者跨部门,跨省、跨国、跨公司等等),都可以用这个思路来需求解决问题的方法。
    
    

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